Font size
WorksheetsDatabase Test 23-MAR-2022
Total questions: 108
Worksheet time: 4hrs 36mins
In a database, Multiple _________ can be created.
Tables
Documents
Primary Key
All of these
___________________ Key uniquely identifies each record in table.
Fields
Record
Row
Primary
In a Database, each row is called a _____________
Field
Data
Record
Table
In a Database, each column is called a _____________
Field
Data
Record
Table
This table has ___________ records.
2
4
5
6
This table has ___________ fields.
2
4
5
6
___________, ___________ & ___________ are fields.
S. No, Product Code & Description
1021, Beverages & Soft drinks, coffees, teas
Dairy Products, Beverages & Seafood.
All of these are fields.
Consider attributes ID, CITY, and NAME. Which one if this can be considered as a primary key?
Name
ID
City
All of these
Which of the following is an example of a DBMS
MS Word
Microsoft Office
Java
Oracle
Database if the collection of information or group of facts in a structured way.
Maybe
No
Yes
Not Always
______________ datatype you'd select for entering "Address".
Number
Date
Text
Time
You have to enter Sam's DOB, What datatype would you use for this field.
Time
Date
Text
Number
______________ symbol indicates that a field is the Primary Key of the table.
All of these
What is this following a definition of?
Stores data/information in an organised way
Field
Database
Record
File/Table
The rows in a database are called:
Records
Fields
Attribute
Coloum
The columns in a database are called:
Records
Fields
Primary key
Foreign key
Data about data is ______________
Data Description
Metadata
Data redundancy
Data inconsistency
Repetition of data is called data ..............
dependance
redundancy
inconsistency
isolation
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 character returns all the data found in a table?
%
#
*
/
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')
In order to modify data in a database we would use which SQL statement?
Update
Amend
Alter
Modify
What is the ID of the records that are returned when the SQL statement "SELECT * FROM Gymnasts WHERE Name='Xavier'"?
0001
0002
0003
0004
0005
What is the ID of the records that are returned when the SQL statement "SELECT * FROM Gymnasts WHERE HighScore > 8.4"?
0001
0002
0003
0004
0005
Which of the following statements will return the most records?
SELECT * FROM Gymnasts WHERE HighScore > 8.0
SELECT * FROM Gymnasts WHERE Competition LIKE R%
SELECT * FROM Gymnasts WHERE Competition LIKE %o%
SELECT * FROM Gymnasts WHERE Name LIKE %i%
Which of the following is NOT a SQL command?
SELECT
FROM
LIKE
SIMILAR
DISTINCT
How many records are there in this table?
6
7
9
42
How many fields are there in this table?
6
7
9
42
How many records would be returned by the following SQL "SELECT * FROM songs WHERE TimesPlayed >=3
0
2
3
4
How many records would be returned by the following SQL "SELECT * FROM songs WHERE Length < 3 AND Protected = True"?
0
2
3
4
What is the purpose of SQL?
To communicate with a game
To communicate with aliens
To communicate with a database
To communicate with a base
What is the select statement used for?
It is used to select a database
It is used to select data from a database
It is used to select a website
It is not a SQL statement
What is the FROM statement used for?
It is used to select a database
It is not a SQL statement
It is used to select a website
It is used to specify which table to select data from.
What would this SQL query display? SQL * FROM Pupils
It would display pupils who have a * in their name.
It is not a SQL statement.
It would display all records from the Pupils table.
It would not display any pupils.
What would this SQL query display? SQL Forename, Surname FROM Pupils
It would only display pupils forename and surname from the Pupils table.
It is not a SQL statement.
It would display all pupils forenames from Pupils table.
It would not display any pupils.
Which SQL query would display all pupils surnames and registration class?
SELECT * FROM Pupils
SELECT * FROM Pupils WHERE surname, regiClass
SELECT surname, regiClass
SELECT surname, regiClass FROM Pupils
Which SQL query would display all pupils details who have the surname Smith?
SELECT surname FROM Pupils WHERE Smith
SELECT * FROM Pupils WHERE surname = Smith
SELECT * FROM Pupils WHERE surname = "Smith"
SELECT * FROM Pupils
Which SQL query would display all pupils who are older than 12?
SELECT * FROM Pupils WHERE age > 12
SELECT * FROM Pupils WHERE age < 12
SELECT * FROM Pupils WHERE age > "12"
SELECT * FROM Pupils WHERE age >= 12
Which SQL query would display all pupils who 12 years old or order and have the surname Smith?
SELECT * FROM Pupils WHERE Age >= 12 AND Surname = "Smith"
SELECT * FROM Pupils WHERE Age >= 12 AND Surname = Smith
SELECT * FROM Pupils WHERE Age > 12 AND Surname = "Smith"
SELECT * FROM Pupils
Which statement will show all fields and records from the table called "celebs"?
SELECT * FROM celebs;
SELECT FROM celebs;
SELECT forename, surname FROM celebs;
SELECT celebs;
The SQL statement being run is:
SELECT player_name, score FROM leaderboard;
What is the name of the table in this query?
SELECT
player_name
score
leaderboard
Which SQL function is used to count the number of rows in a SQL query?
COUNT()
NUMBER()
SUM()
COUNT(*)
The SQL statement to count distinct rows from a column is,
select COUNT DISTINCT columnname AS total_rows from tablename;
select (COUNT DISTINCT columnname) AS total_rows from tablename;
select DISTINCT columnname AS total_rows from tablename;
select COUNT (DISTINCT) AS total_rows from tablename;
The COUNT() function also counts the NULL values.
True
False
The SUM() function returns - - -
some random values from the given column
the total number of columns in the database
the total number of tables in the given databases
the total sum of a given numeric column
The average value of the given numeric column is calculated by using - - - function.
GETAVG()
FINDAVG()
AVG()
AVGVALUE()
What will be the output of the query :
Select Substring('mysql application',3,3)
app
mysql
application
sql
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
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 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 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 ;
Which of the following statements are true about the COUNT function?
The COUNT function counts the number of rows
The COUNT(*) function counts the number of rows with duplicates and NULL values
The COUNT(DISTINCT) function counts the number of distinct rows
COUNT(*) is equivalent to COUNT(ALL)
SELECT *
_______ CUSTOMER;
What is the purpose of the UPDATE command in SQL?
To edit a table name
To change the name of a field
To change the contents of records in a table
To change the structure of a table
UPDATE customer
SET numberOfOrders = 100, discount = "Yes"
WHERE customerID = 50
The explanation of the above code is:
Use the table customer, and update only the record containing customerID = 50. Change the contents of the field numberOfOrders to 100 and discount to "Yes".
Use the table customer. Change the contents of the field numberOfOrders to 100 and discount = "Yes"
Use the table customer, and update only the record containing customerID = 100. Change the contents of the field numberOfOrders to 50 and discount = "Yes"
Use the table customer, and update only the record containing customerID = 50. Change the contents of the field numberOfOrders to 100.
What is the purpose of the GROUP BY statement in SQL?
It groups tables together.
It groups rows that have the same values into summary rows and is often used with aggregate functions.
It must be used when you use ORDER BY.
It is always used when you use an aggregate function.
This SQL code will :
list the total customers
list the number of customers in each country
list all the customerIDs and their country
list list the number of customers in each city
Which of the following are the five built-in functions provided by SQL?
A. COUNT, SUM, AVG, MAX, MIN
B. SUM, AVG, MIN, MAX, MULT
C. SUM, AVG, MULT, DIV, MIN
D. SUM, AVG, MIN, MAX, NAME
Which one of the following sorts rows in SQL?
A.SORT BY
B.ALIGN BY
C.ORDER BY
D.GROUP BY
Which of the following must be enclosed in double quotes?
A) Dates
B) Column Alias
C) Strings
D) All of the above
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;
Identify the two guidelines for group functions and the GROUP BY clause.
You cannot use a column alias in the GROUP BY clause.
The GROUP BY column must be in the SELECT clause.
By using a WHERE clause, you can exclude rows before dividing them into groups.
The GROUP BY clause groups rows and ensures the order of the result set.
If you include a group function in a SELECT clause, you must include a GROUP BY
clause.
