WorksheetsSQL FINAL TEST 1
Total questions: 75
Worksheet time: 3hrs 30mins
Which would show all fields from the Pupils table?
SELECT * FROM Pupils
SELECT Name, Form FROM Pupils
SELECT * FROM *
SELECT * FROM Forms
Which Symbol means "All"
*
&
%
!
Which would find pupils with "er" anywhere in their name?
SELECT * FROM Pupils Where Name = "%er"
SELECT * FROM Pupils Where Name = "$er$"
SELECT * FROM Pupils Where Name = "%er%"
SELECT * FROM Pupils Where Name = "er%"
Choose the correct query
SELECT * FROM EMP WHERE NAME=NULL
SELECT * FROM EMP WHERE NAME IS NULL;
SELECT FROM EMP WHERE NAME IS NULL;
SELECT *FROM EMP WHERE NAME IS 'NULL';
The restrictions or conditions applied on data are known as (a)
The primary key of a table which is present in another table for referencing is known as (a)
What will be the output?
SELECT ROUND(36.7894,2)
36.78
38.79
36.7
36
What will be the output?
SELECT TRUNCATE(36.5643,2);
36.56
36.57
36.5643
36
Which is the correct query to delete the rows of employees whose exp is less than 10.
DELETE * FROM EMP WHERE EXP<10;
DELETE FROM EMP;
DELETE FROM EMP WHERE EXP<10;
DELETE FROM EMP WHERE EXP>10;
Which is not an aggregate function?
SUM()
POW()
AVG()
COUNT()
Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given)
SELECT department_id , max(salary ) FROM employees ;
SELECT department_id , max(salary ) FROM employees GROUP BY department_id ;
SELECT max(salary ) FROM employees GROUP BY department_id ;
SELECT max(salary ) FROM employees ;
Predict the outcome of the below query
SELECT department_id ,avg(salary ) FROM employees HAVING avg(salary )>2000 GROUP BY department_id ORDER BY department_id
It executes successfully.
It throws error because HAVING clause precedes the GROUP BY clause.
It throws error because HAVING clause uses the aggregate function.
It executes but no results are displayed because HAVING clause precedes the GROUP BY clause.
Which clause should you use to exclude group results in a query using group functions?
WHERE
HAVING
GROUP BY
ORDER BY
Predict the outcome of the below query
SELECT department_id , COUNT(first_name ) FROM employees WHERE job IN ('SALESMAN','CLERK','MANAGER','ANALYST') GROUP BY department_id HAVING AVG(salary ) BETWEEN 2000 AND 3000;
It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
It executes successfully.
What will be the output of the query :
Select Substring('mysql application',3,3)
app
mysql
application
sql
What will be the output of the following query:
Select round(59999.99,-2);
59999
59900
60000
59990
Which function will be used to remove only the trailing spaces from a string?
ltrim()
rtrim()
trim()
all
Fill in the blank:
___________________ this clause is used to apply condition on all the rows of table.
Where
Having
Order By
Sort By
Fill in the blank:
(a) this clause is used with GROUP BY to filter the group of records.
What will be the output of following query:
Select length(trim(' exam '));
exam
exam
6
4
Difference between ALTER Table command and UPDATE command :
ALTER is DDL command and is used for modifying the schema of table
UPDATE is DML command and is
used for modifying the existing data of table
Using Alter command any value inside the table can be modified.
Using Update command columns of a table can be renamed.
Which clause is used when we want to use aggregate functions in conditions?
Where
Group By
Having
Both 2 and 3
Match SQL language with SQL commands
1)DDL i)Revok, Grant
2)DML ii)rename a table
3)TCL iii)insert, update
4)DCL iv)rollback , commit
1-iii,2-i,3-ii,4-iv
1-iii,2-ii,3-iv,4-i
1-ii,2-iii,3-iv,4-i
1-i,2-iii,3-ii,4-iv
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
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
The primary key of a table which is present in another table for referencing is known as (a)
From the items_ordered table, select a list of all items purchased for customerid 10449. Display the customerid, item, and price for this customer.
(a)
Select all columns from the items_ordered table for whoever purchased a Tent
(a)
Select the customerid, order_date, and item values from the items_ordered table for any items in the item column that start with the letter “S”.
(a)
Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.
(a)
Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in descending order based on the price.
(a)
Select the item and price of all items that start with the letters ‘S’, ‘P’, or ‘F’
(a)
Select the date, item, and price from the items_ordered table for all of the rows that have a price value ranging from 10.00 to 80.00.
(a)
Select the firstname, city, and state from the customers table for all of the rows where the state value is either: Arizona, Washington, Oklahoma, Colorado, or Hawaii.
(a)
Select the customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table.(HINT : use ALIASES c for customer and i for items_ordered)
(a)
In a LIKE clause, you can could ask for any value ending in “ton” by writing
LIKE ton$
LIKE ^.*ton$
LIKE %ton
LIKE *ton
In a LIKE clause, you can ask for any 6 letter value by writing:
LIKE ??????
LIKE .{6}
LIKE …… (that’s six dots)
LIKE ______ (that’s six underscore characters)
Which statement is used to count number of rows in table?
SELECT COUNT(*) FROM table_name;
SELECT COUNT ALL(*) FROM table_name;
SELECT ROWS(*) FROM table_name;
All the above
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
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%’
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
______________ constraint prevents NULL values
UNIQUE
NOT NULL
NULL
FOREIGN KEY
A primary key can be NULL in the table? TRUE or FALSE
TRUE
FALSE
What is the correct order of clauses in a SQL statement?
SELECT, FROM, ORDER BY, WHERE
SELECT, FROM, WHERE, ORDER BY
SELECT, WHERE, FROM, ORDER BY
WHERE, FROM, SELECT, ORDER BY
With SQL how can I return all items in the Item table sorted from the lowest priced to the highest priced?
SELECT * FROM Items ORDER BY Price ASCENDING
SELECT * FROM Items ORDER BY Price ASC
SELECT * FROM Items BY Price LOWEST TO HIGHEST
SELECT * FROM Items ORDER BY Price DESC
How would you DELETE records from the Items table with a "Chair" Type?
DELETE 'Chair' FROM Items
DELETE Type FROM Items WHERE Type = 'Chair'
DELETE FROM Items WHERE Type = 'Chair'
DELETE ITEMS WHERE Type = 'Chair'
How would you display Chairs in the Items table that have a Price greater than £50.
SELECT * FROM Items WHERE Type = 'Chair' AND Price > 50
SELECT * FROM Items WHERE Type = 'Chair' OR Price < 100
SELECT * FROM Iterms WHERE Type = 'Chair' AND Price >= 50
SELECT * FROM Items WHERE Price > 50
Which query can be used to get the above result?
SELECT e.fname as employee, m.fname as manager
FROM emp e INNER JOIN emp m
ON e.manager_id=m.empno;
SELECT e.fname as employee, m.fname as manager
FROM emp1 e INNER JOIN emp2 m
ON e.manager_id=m.empno;
SELECT e.fname as employee, m.fname as manager
FROM emp2 e INNER JOIN emp1 m
ON e.manager_id=m.empno;
SELECT e.fname as employee, m.fname as manager
FROM emp e, emp m
ON e.manager_id=m.empno;
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
You are required to update the phone number for only the DesignerID "SMI01" in the "Designer" table. Which of these would successfully do that?
UPDATE Designer SET PhoneNo = '01224123456'
UPDATE Designer (PhoneNo) VALUES ('01224123456')
UPDATE Designer SET PhoneNo = '01224123456' WHERE DesignerID = 'SMI01'
UPDATE PhoneNo = '01224123456' FROM Designer WHERE DesignerID = 'SMI01'
What would the SQL query pictured do?
Find all the men
Find all of the female writers
Find all of Elgar's friends
Find all the male writers
Find all the fish
What is the most common JOIN statement used in business?
Inner Join
Left Join
Right Join
Full Outer Join
Which join would you use to create a query that returns ALL data from Table B and only those on Table A that match?
Inner Join
Left Join
Right Join
Full Outer Join
________ is a subquery.
Sub part of a query
Query within the query
Clause
None of the above
Which is/are part of subquery?
Inner query
Outer query
Both Inner query & Outer query
None of the above
Which of the following WHERE clauses statements is valid?
WHERE A2 IN (SELECT B2 ...
WHERE A2 EXISTS (SELECT B2 ...
WHERE EXISTS (SELECT B2 ...
WHERE A2 EXISTS IN (SELECT B2...
Identify the SELECT statements that execute successfully
SELECT first_name, last_name, job_id, salary*12 AS Yearly Sal
FROM employees;
SELECT first_name, last_name, job_id, salary*12 "yearly sal"
FROM employees;
SELECT first_name, last_name, job_id, salary AS "yearly sal"
FROM employees;
SELECT first_name+last_name AS name, job_Id, salary*12 yearly sal
FROM employees;
You can use constraints to do the following:
Enforce rules on the data in a table whenever a row is inserted, updated, or deleted.
Prevent the deletion of a table.
Prevent the creation of a table.
Prevent the creation of data in a table.
What is a foreign key?
A FOREIGN KEY is a field in one table that matches the PRIMARY KEY in another table.
A foreign key is a field in a table that uniquely defines every record uniquely
Which constraint(s) can be used to make sure that the column is not left empty?
NOT NULL
UNIQUE
PRIMARY KEY
ALL THE ABOVE
6. ___________ which helps to uniquely identify a record.
Primary key Constraint
Unique Constraint
Default constraint
Check constraint
7. _________ helps to set a limit value placed for a field.
DEFAULT Constraint
Primary Key Constraint
Check Constraint
TABLE CONSTRAINT
SELECT * FROM cars WHERE Doors > 3
would return how many records?
1
2
3
4
SELECT * FROM Students WHERE Gender="Female" AND Class=2.2
would return how many records?
1
2
3
4
Repetition of data is called data ..............
dependance
redundancy
inconsistency
isolation
A ............ is an organized collection of structured data.
information
file
database
DBMS
A database ............... is a sketch of design of a planned data.
instance
schema
metadata
relation
A data ................ is a set of rules that define valid data.
constraint
data dictionary
query
all of these
A relational database consists of a collection of ..............
tables
fields
records
keys
Data redundancy may lead to data inconsistency.
True
False
This symbol is placed immediately after the SELECT command to display all the fields.
$
%
=
*
