Order By clause is used to sort the retrieved data in ascending or descending order. Group By clause is used to group the result-set by one or more columns. Order and Group are not valid SQL commands.
3) a) True
NULL signifies an unknown value or a value which doesn’t exist during the creation of the record.
4) c) Drop
5) a) <>
6) c) Select top
7) d) Limit
All database systems does not supports SELECT TOP clause. It can be used in SQL. Mysql supports the LIMIT clause to fetch a limited number of records from the database table.
8) d) All correct
9) b) Selects a record if both conditions are true
10) b) Adds a value to a column’s existing value
11) a) True
12) d) No syntax error
13) d) No error
14) a) XOR operator
SQL does not have an XOR logical operator. Use AND/OR instead.
15) d) All Correct
The compound operator ‘*=’ is correctly used to double the salary of specified employees.
16) a) It functions as a WHERE clause
Without GROUP BY, HAVING functions like a WHERE clause, filtering rows based on specified conditions.
17) a) The query fails
Using a column in SELECT that is not part of GROUP BY without an aggregate function causes the query to fail.
18) c) GROUP BY Salary
GROUP BY should be used with the column mentioned in SELECT, here it should be GROUP BY Department.
19) d) HAVING AverageSalary > 50000
HAVING should be used with an aggregate function directly, not with an alias.
20) a) SELECT Name
Name should be included in the GROUP BY clause or used within an aggregate function.
21) d) No Error
The query correctly groups by Department and uses HAVING to filter groups.
22) d) All Correct
ORDER BY 3 is correct, indicating ordering by the third column in the SELECT list.
23) c) WHERE MAX(Salary) > 50000
Aggregate functions such as MAX should be used in the HAVING clause when filtering groups created by the GROUP BY clause.
24) c) No error
The query correctly uses HAVING with ALL to compare counts across departments.
25) c) HAVING clause misuse
The original statement had an error in the HAVING clause. It incorrectly used the alias ‘TotalEmployees’. The correct HAVING clause should be “HAVING COUNT(*) > 5”.
Welcome to the SQL Quiz! This blog post features 25 multiple-choice questions that explore essential concepts of SQL.
1. Which of the following statement is true about views in SQL?
Select the best answer
a) We can delete but not insert rows in a view b) We cannot insert and delete rows in a view c) We can insert but not delete rows in a view d) We can insert and delete rows in a view
SELECT Department, COUNT(EmployeeID) FROM Employees GROUP BY Department HAVING COUNT(EmployeeID) > ALL (SELECT COUNT(EmployeeID) FROM Employees GROUP BY Department);
Choose one option
a) ALL operator b) COUNT(EmployeeID) c) No error d) subquery
1) c) Organized collection of data or information that can be accessed, updated, and managed
2) c) Table
3) a) All Correct
4) c) WHERE clause
The WHERE clause is incomplete and needs a condition to specify which records to update.
5) a) True
6) a) SELECT * FROM Employees WHERE FirstName=’Peter’ AND LastName=’Jackson’
7) a) All Correct
8) b) >= ‘2023-06-01’
The OR operator should be followed by a complete condition, including the column name. SELECT * FROM Orders WHERE OrderDate >= ‘2023-01-01’ OR OrderDate >= ‘2023-06-01’;
9) c) HAVING
10) c) No error
11) a) SELECT * FROM Employees WHERE LastName BETWEEN ‘Hansen’ AND ‘Pettersen’
12) a) SELECT DISTINCT
13) a) ORDER BY
14) a) SELECT * FROM Employees ORDER BY FirstName DESC
15) a) INSERT INTO Employees VALUES (‘Jimmy’, ‘Jackson’)
16) a) INSERT INTO Persons (LastName) VALUES (‘Olsen’)
17) c) To give users access privileges
18) b) Removes specific access privileges from users
19) d) All Correct
20) c) ON Database
This is because the REVOKE statement usually specifies the type of object (such as a) specific table or schema) with tables). Just mentioning Database without specifying an object within it is incorrect. The corrected syntax typically needs to look like:
REVOKE INSERT, UPDATE ON Database.* FROM user123;
21) a) UPDATE Employees SET LastName=’Nilsen’ WHERE LastName=’Hansen’
22) a) DELETE FROM Employees WHERE FirstName = ‘Peter’
Welcome to the SQL Quiz! This blog post features 25 multiple-choice questions that explore essential concepts of SQL.
1. What is a database?
Select the best answer
a) Organized collection of information that cannot be accessed, updated, and managed b) Collection of data or information without organizing c) Organized collection of data or information that can be accessed, updated, and managed d) Organized collection of data that cannot be updated
6. With SQL, how do you select all the records from a table named “Employees” where the “FirstName” is “Peter” and the “LastName” is “Jackson”?
Choose one option
a) SELECT * FROM Employees WHERE FirstName='Peter' AND LastName='Jackson'
b) SELECT FirstName='Peter', LastName='Jackson' FROM Employees
c) SELECT * FROM Employees WHERE FirstName<>'Peter' AND LastName<>'Jackson'
d) SELECT * FROM Employees WHERE FirstName='Jackson' AND LastName='Peter'
11. With SQL, how do you select all the records from a table named “Employees” where the “LastName” is alphabetically between (and including) “Hansen” and “Pettersen”?
Choose one option
a) SELECT * FROM Employees WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
b) SELECT * FROM Employees WHERE LastName>'Hansen' AND LastName<'Pettersen'
c) SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Employees
d) None
14. With SQL, how can you return all the records from a) table named “Employees” sorted descending by “FirstName”?
Choose one option
a) SELECT * FROM Employees ORDER BY FirstName DESC
b) SELECT * FROM Employees SORT 'FirstName' DESC
c) SELECT * FROM Employees SORT BY 'FirstName' DESC
d) SELECT * FROM Employees ORDER FirstName DESC
15. With SQL, how can you insert a new record into the “Employees” table?
Choose one option
a) INSERT INTO Employees VALUES ('Jimmy', 'Jackson')
b) INSERT VALUES ('Jimmy', 'Jackson') INTO Employees
c) INSERT ('Jimmy', 'Jackson') INTO Employees
d) INSERT INTO Table Employees VALUES ('Jimmy', 'Jackson')
16. With SQL, how can you insert “Olsen” as the “LastName” in the “Persons” table?
Choose one option
a) INSERT INTO Persons (LastName) VALUES ('Olsen')
b) INSERT ('Olsen') INTO Persons (LastName)
c) INSERT INTO Persons ('Olsen') INTO LastName
d) All incorrect
21. How can you change “Hansen” into “Nilsen” in the “LastName” column in the Employees table?
Choose one option
a) UPDATE Employees SET LastName='Nilsen' WHERE LastName='Hansen'
b) MODIFY Employees SET LastName='Nilsen' WHERE LastName='Hansen'
c) UPDATE Employees SET LastName='Hansen' INTO LastName='Nilsen'
d) MODIFY Employees SET LastName='Hansen' INTO LastName='Nilsen
22. With SQL, how can you delete the records where the “FirstName” is “Peter” in the Employees Table?
Choose one option
a) DELETE FROM Employees WHERE FirstName = 'Peter'
b) DELETE FirstName='Peter' FROM Employees
c) DELETE ROW FirstName='Peter' FROM Employees
d) All are incorrect
14. What is the difference between the DROP and TRUNCATE commands in SQL?
Choose one option
a) DROP deletes the table, TRUNCATE deletes only the table data b) TRUNCATE deletes the table, DROP deletes only the table data c) No difference d) Both commands modify table data
18. With SQL, how do you select all the records from a table named “Students” where the value of the column “FirstName” is “Peter”?
Choose one option
a) SELECT * FROM Students WHERE FirstName='Peter'
b) SELECT * FROM Students WHERE FirstName<>'Peter'
c) SELECT [all] FROM Students WHERE FirstName LIKE 'Peter'
d) SELECT [all] FROM Students WHERE FirstName='Peter'
19. With SQL, how do you select all the records from a table named “Students” where the value of the column “FirstName” starts with an “a”?
a) SELECT * FROM Students WHERE FirstName LIKE 'a%'
b) SELECT * FROM Students WHERE FirstName='%a%'
c) SELECT * FROM Students WHERE FirstName='a'
d) SELECT * FROM Students WHERE FirstName LIKE '%a'
20. How does a transaction ensure data integrity in SQL?
Choose one option
a) By locking the database b) By allowing multiple users to modify data simultaneously c) By ensuring all operations within the transaction are completed before committing d) By automatically updating all related tables
QA Automation Expert 