Font size
WorksheetsDBMS With SQL Test 03(30-JUN-2021)
Total questions: 70
Worksheet time: 36mins
Which statement is used to extract data from a database?
Extract
Get
Open
Select
If you were wanting to filter data, which clause would you use?
Where
Order by
From
Select
Using which word allows you to specify that more than one condition must be met in a query?
Also
And
Or
Where
Using which word allows you to specify that only one of a series of conditions needs to be met in a query filter?
And
Maybe
If
Or
In order to amend data in a database we would use which SQL statement?
Update
Amend
Alter
Modify
To remove a record from a table which SQL statement would you use?
Remove
Cancel
Delete
Eradicate
Which statement allows us to add a record to a table?
Add To
Update To
Add Into
Insert Into
Less than or equal to?
>=
=>
=<
<=
Which word is missing from the following SQL statement?
Select * table_name
With
Where
From
And
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
How do we select all rows for the "Designer" table?
SELECT * FROM Designer
SELECT [All] FROM Designer
SELECT Designer.*
SELECT FROM Designer
How would we script a SQL query to select "Description" from the Item table?
SELECT Item.Description
EXTRACT Description FROM Item
SELECT Item FROM Description
SELECT Description FROM Item
Which query would return only the fields DesignerID and Designer?
SELECT * FROM Designers
SELECT DesignerID, Name FROM Designers
SELECT DesignerID AND Designer FROM Designers
SELECT DesignerID, Designer FROM Designers
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'
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 SQL statement would add a record to the database?
INSERT INTO Designer VALUES ('SMI01', 'M Smith', 'mail@msmith.com', '01224123456')
INSERT VALUES ('SMI01', 'M Smith', 'mail@msmith.com', 01224123456') INTO Designer
INSERT ('SMI01', 'M Smith', 'mail@msmith.com', '01224123456') INTO Designer
INSERT Designer INTO VALUES (''SMI01', 'M Smith', 'mail@msmith.com', '01224123456')
Which SQL function is used to count the number of rows in a SQL query?
COUNT()
NUMBER()
SUM()
COUNT(*)
Which of the following SQL clauses is used to DELETE tuples from a database table?
DELETE
REMOVE
DROP
CLEAR
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
The UPDATE SQL clause can _____________
update only one row at a time
update more than one row at a time
delete more than one row at a time
delete only one row at a time
How can you change “Hansen” into “Nilsen” in the “LastName” column in the Persons table?
UPDATE Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
MODIFY Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
MODIFY Persons SET LastName=’Hansen’ INTO LastName=’Nilsen’
UPDATE Persons SET LastName=’Nilsen’ WHERE LastName=’Hansen’
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
SQL query to find the temperature in increasing order of all cities.
SELECT city FROM weather ORDER BY temperature
SELECT city, temperature FROM weather
SELECT city, temperature FROM weather ORDER BY temperature
SELECT city, temperature FROM weather ORDER BY city
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
The command to remove rows from a table ‘CUSTOMER’ is __________________
DROP FROM CUSTOMER
UPDATE FROM CUSTOMER
REMOVE FROM CUSTOMER
DELETE FROM CUSTOMER WHERE
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’);
Match the following SQL term with a description - FROM
The criteria which must be met
The fields to display
The field and order used to sort the results
The table used in the query
Match the following SQL term with a description - SELECT
The fields to display
The table used in the query
The criteria which must be met
The field and order used to sort the results
Match the following SQL term with a description - WHERE
The table used in the query
The fields to display
The field and order used to sort the results
The criteria which must be set
Match the following SQL term with a description - ORDER BY
The fields to display
The field and order used to sort the results
The table used in the query
The criteria that must be met
In what order does SQL process the instructions?
FROM - SELECT - WHERE - ORDER BY
WHERE - FROM - SELECT - ORDER BY
SELECT - FROM - WHERE - ORDER BY
SELECT - FROM - ORDER By - WHERE
What is a field?
A collection of information
A single item of information
A list of information
A group of records
Which of the following is not a database data type?
text
number
array
date
Which example of the field name below is in camel case?
First Name
firstName
FIRSTNAME
firstname
Which symbol would you use to represent more than 50
= 50
< 50
> 50
# 50
How to select only rows which are equal by the condition?
SELECT * FROM my_table_name WHERE city = 'Berlin'
SELECT * FROM my_table_name WHERE city LIKE 'Berlin'
SELECT * FROM my_table_name WHERE city EQUAL 'Berlin'
SELECT * FROM my_table_name WHERE city EQUALS TO 'Berlin'
How to select only rows which aren't equal by the condition?
SELECT * FROM my_table_name WHERE city <> 'Berlin'
SELECT * FROM my_table_name WHERE city NOT LIKE 'Berlin'
SELECT * FROM my_table_name WHERE city NOT EQUAL 'Berlin'
SELECT * FROM my_table_name WHERE city NOT IN ('Berlin')
How to select only specific columns from the table?
SELECT city, country FROM customers
SELECT customers.city, customers.country FROM customers
SELECT c.city, c.country FROM customers c
SELECT COLUMNS city, country FROM customers
How to select values by many conditions? (choose all possible options)
SELECT * FROM Products WHERE price = 18 OR price = 19
SELECT * FROM Products WHERE price = 18 BETWEEN name LIKE 'nik'
SELECT * FROM Products WHERE price = 18 AND name LIKE 'nik'
SELECT * FROM Products WHERE price = 18 IN name LIKE 'nik'
Which of the proposed statements can return a result that is less or more than the given value?
SELECT * FROM Products WHERE price < 18
SELECT * FROM Products WHERE price LESS THEN 18
SELECT * FROM Products WHERE price BY 18
SELECT * FROM Products WHERE price BETWEEN 18 AND 100
How to select only NULL values?
SELECT * FROM Products WHERE price = NULL
SELECT * FROM Products WHERE price LIKE NULL
SELECT * FROM Products WHERE price IS NULL
SELECT * FROM Products WHERE price EQUALS NULL
How to select values by the last and first symbols in the char field?
SELECT * FROM Customers WHERE city LIKE 'B%'
SELECT * FROM Customers WHERE city LIKE 'B%n'
SELECT * FROM Customers WHERE city LIKE '%n'
SELECT * FROM Customers WHERE city LIKE 'B_%n'
How to select values from the specific range?
SELECT * FROM Products WHERE price IN (18, 22)
SELECT * FROM Products WHERE price BETWEEN 18
AND 22
SELECT * FROM Products WHERE price >= 18 AND
price <= 22
SELECT * FROM Products WHERE price >= 18 OR
price <= 22
DDL means
(a)
Which of the following is NOT a GROUP BY function?
MAX
MIN
NVL
AVG
Which of the following functions can be used without GROUP BY clause in SELECT query? (Select all that apply)
COUNT
MAX
MIN
AVG
Which of the following is not a valid SQL statement?
SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;
SELECT MIN(pubdate) FROM books WHERE category = 'COOKING';
SELECT COUNT(*) FROM orders WHERE customer# = 1005;
SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;
Which of the following are true about group functions?
You can use group functions in any clause of a SELECT statement.
You can use group functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
You can mix single row columns with group functions in the column list of a SELECT statement by grouping on the single row columns.
You can pass column names, expressions, constants, or functions as parameter to an group function.
Which keyword is used to eliminate duplicate value?
Distinct
All
Top
Having
Which function is used to display number of record?
Count
Avg
Total
Count(*)
What is the value of query “select round(23463.342,-2)”?
23463.34
23463.3
23500
23464
When examining a row in a table, we refer to it as a...
row number
result number
record
result
When examining a column in a table, we refer to it as a...
field name
folder name
column number
table name
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
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)
Which SQL function is used to find the average value of any column?
Mean()
Avg()
Average()
Sum()
Which would show all fields from the Pupils table?
SELECT * FROM Pupils
SELECT Name, Form FROM Pupils
SELECT * FROM *
SELECT * FROM Forms
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';
Consider the following SQL statement. What type of statement is this? SELECT * FROM Employee;
DML
DDL
DCL
TCL
Which of the following query contains error?
SELECT * FROM Emp WHERE Empid=1001;
SELECT Empid FROM Emp WHERE Empid=1001;
SELECT Empid FROM Emp;
SELECT Empid WHERE empid=1001;
Which operator performs pattern matching?
BETWEEN
LIKE
EXISTS
None of These
______________ constraint prevents NULL values
UNIQUE
NOT NULL
NULL
FOREIGN KEY
A primary key can be NULL in the table? TRUE or FALSE
TRUE
FALSE
