WorksheetsQuiz on SQL
Total questions: 45
Worksheet time: 45mins
The language used application programs to request data from the DBMS is referred to as __________
DDL
DML
Query Language
All of the mentioned
Which of the following is not a type of SQL statement?
Data Manipulation Language (DML)
Data Definition Language (DDL)
Data Control Language (DCL)
Data Communication Language (DCL)
In existing table, ALTER TABLE statement is used to
Add columns
Add constraints
Delete columns
Delete constrains
All the above
SQL Query to delete all rows in a table without deleting the table (structure, attributes, and indexes)
DELETE FROM table_name;
DELETE TABLE table_name;
DROP TABLE table_name;
None
Correct syntax query syntax to drop a column from a table is
DELETE COLUMN column_name;
DROP COLUMN column_name;
ALTER TABLE table_name DROP COLUMN column_name;
None is correct.
Which command is used for removing a table and all its data from the database:
Create command
Drop table command
Alter table command
All of the mentioned
Which of the following language is used to specify database Schema ?
DDL
DML
DCL
None
Which SQL statement is used to delete data from a database?
DELETE
COLLAPSE
DROP
REMOVE
With SQL, how do you select a column named "FirstName" from a table named "Persons"?
SELECT FirstName FROM Persons
SELECT Persons.FirstName
SELECT Persons Where FirstName
SELECT Persons From FirstName
Which SQL statement is used to insert new data in a database?
ADD RECORD
INSERT INTO
ADD NEW
INSERT NEW
With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?
SELECT * FROM Persons WHERE FirstName<>'Peter'
SELECT * FROM Persons WHERE FirstName LIKE 'Peter'
SELECT * FROM Persons WHERE FirstName='Peter'
SELECT [all] FROM Persons WHERE FirstName='Peter'
The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true
True
False
Both A and B
None of these
With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?
SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'
SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson'
SELECT FirstName='Peter', LastName='Jackson' FROM Persons
SELECT*FROM Persons WHERE FirstName='Peter' OR LastName='Jackson'
With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons
SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'
SELECT [all] FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
With SQL, how can you insert a new record into the "Persons" table?
INSERT ('Jimmy', 'Jackson') INTO Persons
INSERT VALUES ('Jimmy', 'Jackson') INTO Persons
INSERT INTO Persons VALUES ('Jimmy', 'Jackson')
INSERT INTO Persons VALUES
With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?
INSERT INTO Persons (LastName) VALUES ('Olsen')
INSERT ('Olsen') INTO Persons (LastName)
INSERT INTO Persons ('Olsen') INTO LastName
INSERT IN Persons ('Olsen') TO LastName
Which operator is used to select values within a range?
WITHIN
RANGE
ORDER BY
BETWEEN
Which is not a DDL Command?
CREATE
ALTER
DROP
UPDATE
To remove duplicate rows from the results of an SQL SELECT statement, the ________ qualifier specified must be included
ONLY
UNIQUE
DISTINCT
SINGLE
With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?
SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'
SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName<'Pettersen'
SELECT LastName>'Hansen' AND LastName<'Pettersen' FROM Persons
With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?
SELECT * FROM Persons SORT 'FirstName' DESC
SELECT * FROM Persons ORDER BY FirstName DESC
SELECT * FROM Persons SORT BY 'FirstName' DESC
SELECT * FROM Persons ORDER FirstName DESC
With SQL, how can you return the number of records in the "Persons" table?
Next ❯
SELECT NO(*) FROM Persons
SELECT COLUMNS(*) FROM Persons
SELECT COUNT(*) FROM Persons
SELECT LEN(*) FROM Persons
The NOT NULL constraint enforces a column to not accept empty values.
Next ❯
TRUE
FALSE
Which operator is used to search for a specified pattern in a column?
GET
FROM
LIKE
With SQL, how do you select all the records from a table named “Persons” where the value of the column “FirstName” ends with an “a”?
SELECT * FROM Persons WHERE FirstName=’a’
SELECT * FROM Persons WHERE FirstName LIKE ‘a%’
SELECT * FROM Persons WHERE FirstName LIKE ‘%a’
SELECT * FROM Persons WHERE FirstName=’%a%’
In the given query which of the keyword has to be inserted?
INSERT INTO employee _____ (1002,Joey,2000);
Table
Values
Relation
Field
CREATE TABLE employee (id INTEGER,name VARCHAR(20),salary NOT NULL);
INSERT INTO employee VALUES (1005,Rach,0);
INSERT INTO employee VALUES (1007,Ross, );
INSERT INTO employee VALUES (1002,Joey,335);
Some of these insert statements will produce an error. Identify the statement.
Insert into employee values (1005,Rach,0);
Insert into employee values (1002,Joey,335);
Insert into employee values (1007,Ross, );
None of the mentioned
The SQL WHERE clause:
limits the column data that are returned.
limits the row data that are returned.
Both A and B are correct
Neither A nor B is correct
How can you change 'Thomas' to 'Michael' in the LastName column in the user table
UPDATE User SET LastName = 'Thomas' INTO LastName = 'Michel'
MODIFY Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
MODIFY Users SET LastName = 'Thomas' INTO LastName = 'Michel'
UPDATE Users SET LastName = 'Michel' WHERE LastName = 'Thomas'
Which constraint(s) can be used to make sure that the column is not left empty?
NOT NULL
UNIQUE
PRIMARY KEY
ALL THE ABOVE
5. ___________ constraint ensures that no two rows have the same value in the specified columns.
Primary Key
Unique
Default
Check
If you don’t specify ASC or DESC after a SQL ORDER BY clause, the following is used by default
ASC
DESC
There is no default value
None of the mentioned
What does the ALTER TABLE clause do?
The SQL ALTER TABLE clause modifies a table definition by altering, adding, or deleting table columns and/or constraints
The SQL ALTER TABLE clause is used to insert data into database table
THE SQL ALTER TABLE deletes data from database table
The SQL ALTER TABLE clause is used to delete a database table
SQL query to find all the cities whose humidity is 95.
SELECT city WHERE humidity = 95
SELECT city FROM weather WHERE humidity = 95
SELECT humidity = 89 FROM weather
SELECT city FROM weather
Find the name of those cities with temperature and condition whose condition is either sunny or cloudy but temperature must be greater than 70.
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ AND condition = ‘cloudy’ OR temperature > 70
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ OR temperature > 70
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ OR condition = ‘cloudy’ AND temperature > 70
SELECT city, temperature, condition FROM weather WHERE condition = ‘sunny’ AND condition = ‘cloudy’ AND temperature > 70
Find the names of these cities with temperature and condition whose condition is neither sunny nor cloudy.
SELECT city, temperature, condition FROM weather WHERE condition NOT IN (‘sunny’, ‘cloudy’)
SELECT city, temperature, condition FROM weather WHERE condition NOT BETWEEN (‘sunny’, ‘cloudy’)
SELECT city, temperature, condition FROM weather WHERE condition IN (‘sunny’, ‘cloudy’)
SELECT city, temperature, condition FROM weather WHERE condition BETWEEN (‘sunny’, ‘cloudy’);
What is the difference between WHERE and HAVING?
HAVING only applies to aggregate functions like GROUP BY
There are interchangeable
WHERE specifies a GEOGRAPHIC location for a database server
WHERE has limited use
Why make views?
It is faster, safer and better practice to not allow general access to the production database
You should not, they are slow
You should not, they are inefficient
"View" is simply a synonym for the return of the SELECT function
ORDER BY specifies ...................................
the criteria you need to find the information
the table(s) that contain(s) the information
the information you want to find within the database
how the data is organized (sorted) when it is retrieved.
Given the following SQL query, what will be the result of the subquery?
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
All employees with salaries less than the average
All employees with salaries greater than the average
An error
Which query returns the total sales amount for each product but includes only products where the total sales exceed $1000?
SELECT product_id, COUNT(amount)
FROM sales
GROUP BY product_id
HAVING COUNT(amount) > 1000;
SELECT product_id, SUM(amount)
FROM sales
WHERE SUM(amount) > 1000
GROUP BY product_id;
SELECT product_id, SUM(amount)
FROM sales
GROUP BY product_id
HAVING SUM(amount) > 1000;
SELECT product_id, SUM(amount)
FROM sales
GROUP BY product_id
WHERE SUM(amount) > 1000;
Which SQL query will return the second-highest salary from the employees table?
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
SELECT MAX(salary)
FROM employees
ORDER BY salary DESC
LIMIT 1, 1;
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
SELECT salary
FROM employees
WHERE salary < MAX(salary);
Which join refers to join records from the right table that have no matching key in the left table are include in the result set:
Left outer join
Right outer join
Full outer join
None of the above
Which join is to be used between two tables A and B when the resultant table needs rows from A and B that matches the condition and rows from A that does not match the condition?
Outer Join
Cross Join
Inner Join
None of the above
Which of the following statements are true?
INNER JOIN only retrieves those rows from Cartesian Product that satisfy the JOIN condition
FULL OUTER JOIN is same as CROSS JOIN
SELF JOIN is a special type of OUTER JOIN
Both A and C
