Test: SQL - 3 - Computer Science Engineering (CSE) MCQ

Aggregate functions are functions that take a collection (a set or multiset) of values as input and return a single value. SQL offers five built-in aggregate functions:

• Average: avg
• Minimum: min
• Maximum: max
• Total: sum
• Count: count

Key Points

• An Integrity constraint is a rule that ensures the accuracy and consistency of data within a relational database.
• In this context, the constraint ensures that an employee's salary should not exceed Rs. 12,000.
• Integrity constraints can be applied to ensure that the data entered into a database adheres to certain rules or conditions.

Additional Information

• Referential constraint: This type of constraint ensures that a foreign key value always points to an existing row in another table, maintaining referential integrity between tables.
• Over-defined constraint: This term is not commonly used in database terminology. It might imply an excessive or redundant constraint, but it is not a standard term.
• Feasible constraint: This term suggests a constraint that is practical and possible to implement but does not specifically refer to database constraints.

Key Points

• DDL stands for Data Definition Language.
• DDL commands are used to define and modify the structure of a database, such as creating or removing tables and specifying the data types of columns.
• Update is a DML (Data Manipulation Language) command.
• DML commands are used to manipulate the data within a database, such as inserting, updating, and deleting records.
• The other options (Drop, Alter, Create) are all DDL commands.

Breakdown of all the options:

• Drop: This command removes a database object, such as a table.
• Alter: This command modifies the structure of an existing database object.
• Update: This command modifies the data within a table. (Not a DDL command it is DML command)
• Create: This command creates a new database object, such as a table.

Key Points
The commonly used wildcard characters in SQL LIKE:

• %: Matches zero, one, or more characters.
• _: Matches a single character (any letter, number, or symbol).

Examples:

• SELECT * FROM customers WHERE name LIKE '%en%'; - This query will find all customer names that contain the letters "en" anywhere in the name (e.g., "John", "Steven", "Weekend").
• SELECT * FROM products WHERE code LIKE 'PR%'; - This query will find all product codes that start with "PR" followed by any characters (e.g., "PR123", "PR-ABC").
• SELECT * FROM users WHERE username LIKE 'user_'; - This query will find all usernames that start with "user_" followed by a single character (e.g., "user_a", "user_1").

The * symbol is used to see every column of a table.
 For Example, Let us consider a table Table1

If you want to fetch only some specific columns from the table, then we can use this query,
Select ID,NAME,AGE from Table1; // Syntax is Select (column_name1, coloumn_name2.....coloum_name) from table_name;

If you want to fetch all the fields of the Table1 table, then you should use the following query , 
Select * from Table1;    // Syntax is Select * from table_name;

Therefore Option 3 is correct

Without "having" clause query calculates the average age (where age >= 18) and groups by ratings so table returned is:

After applying "having" clause table returned is:

GROUP BY clause:

• The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
• The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)

Example:
SELECT COMPANY, COUNT(*)  
FROM PRODUCT_MAST   
GROUP BY COMPANY 

Therefore all attributes used in the GROUP BY clause must appear in the SELECT clause 

HAVING clause:
HAVING clause is used to specify a search condition for a group or an aggregate.
Having is used in a GROUP BY clause.
If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.

Syntax:
SELECT column1, column2   
FROM table_name  
WHERE conditions   
GROUP BY column1, column2   
HAVING conditions  
ORDER BY column1, column2;  
 
Example:
SELECT COMPANY, COUNT(*)  
FROM PRODUCT_MAST   
GROUP BY COMPANY  
HAVING COUNT(*)>2; 
Therefore an SQL query can contain a HAVING clause only if it has a GROUP BY clause.

Concept:
In SQL selection is achieved by SELECT..... FROM... WHERE. SQL selection is implemented through the WHERE clause of a SELECT statement. 

Example: 
Select *
from student
where course_name= "DBMS";
A basic selection of select statements can be implemented with from and where clause. The SQL SELECT command retrieves records from one or more tables and returns them as a result set. A SELECT command gets zero or more rows from one or more database tables or views. The most frequent data manipulation language (DML) command is SELECT in most applications.
Hence the correct answer is SELECT..... FROM... WHERE.

SQL (Structured Query Language) is a standardized programming language that's used to manage relational databases and perform various operations on the data in them. ... SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s.

• SQL is regularly used not only by database administrators, but also by developers writing data integration scripts and data analysts looking to set up and run analytical queries.
• The uses of SQL include modifying database table and index structures; adding, updating and deleting rows of data; and retrieving subsets of information from within a database for transaction processing and analytics applications.

Hence the correct answer is Structured Query Language.

TRUNCATE

• Is a DDL command hence it cannot be rolled back.
• It resets the identity of the table and locks that state of the table.
• Hence, Commit and Rollback will have no effect after TRUNCATE.
   

DELETE

• Is a DML command hence it can be rolled back
• It does not rest the identity of the table, it just locks the table row
• Hence Commit and Rollback can have effect depending on the lock techniques used.

Hence, it can be said that after the execution of ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data, while ‘DELETE’ allows it.

It can also be said that after the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data, while TRUNCATE does not allow it

NOTE
In official ISRO CS 2020, both option 1 and 3 were correct and hence option is slightly modified to get only correct answer.