WorksheetsDatabase Constraints Quiz
Total questions: 86
Worksheet time: 43mins
What is the purpose of constraints in a database table?
To specify rules for the data in a table
To delete data from a table
To create relationships between tables
To format the data in a table
Which of the following ensures the accuracy and reliability of the data in a table?
Constraints
Primary key
Foreign key
Index
What does a primary key do in a database table?
Refers to the primary key in another table
Uniquely identifies each row in the table
Limits the type of data in the table
Specifies rules for the data in the table
What is the role of a foreign key in a database table?
Specifies rules for the data in a table
Refers to the primary key in another table and relates two tables
Uniquely identifies each row in the table
Ensures the accuracy and reliability of the data
What does the UNIQUE constraint in MySQL ensure?
All values in a column are unique/different.
A column cannot have a NULL value.
A default value is set for a column when no value is specified.
All values in a column are identical.
Which constraint ensures that a column cannot have a NULL value in MySQL?
UNIQUE
NOT NULL
DEFAULT
PRIMARY KEY
What is the purpose of the DEFAULT constraint in MySQL?
To ensure all values in a column are unique.
To prevent NULL values in a column.
To set a default value for a column when no value is specified.
To enforce a primary key in a table.
What do NULL values represent in MySQL?
Missing unknown data.
Duplicate data.
Default values.
Primary key values.
What is the primary purpose of the AUTO_INCREMENT feature in a database table?
To allow duplicate values in a column.
To automatically generate a unique number for each new record.
To delete records automatically.
To sort records in ascending order.
By default, what is the starting value for AUTO_INCREMENT in a database table?
0
1
10
100
What is AUTO_INCREMENT most commonly used for in a database table?
Generating a unique primary key for each new record.
Sorting records alphabetically.
Creating foreign keys automatically.
Deleting duplicate records.
What is the purpose of the `AUTO_INCREMENT` attribute in the `CREATE TABLE` statement?
To ensure a column is unique.
To automatically generate sequential values for a column.
To set a default value for a column.
To define a foreign key relationship.
Which SQL keyword is used to specify the database to work with in the given example?
SELECT
USE
CREATE
INSERT
In the `CREATE TABLE groups` statement, what does the `UNIQUE` constraint ensure for the `name` column?
The column can only contain numeric values.
The column cannot have duplicate values.
The column must have a default value.
The column cannot be null.
What is the default value assigned to the `profile` column in the `groups` table?
NULL
"IT"
"Default"
"Profile"
What type of relationship is established between the `students` table and the `groups` table using the `FOREIGN KEY` constraint?
One-to-one relationship.
One-to-many relationship.
Many-to-many relationship.
No relationship.
Which data type is used for the `DOB` column in the `students` table?
VARCHAR
INT
DATE
TEXT
What is the purpose of the "INSERT INTO" statement in SQL?
To delete data from a table
To update data in a table
To add new data to a table
To retrieve data from a table
In the SQL statement "INSERT INTO groups (name, profile) VALUES ('C1', 'computing');", what does "groups" represent?
A column name
A table name
A database name
A function name
What happens if a value for a column with a default value is not provided in an "INSERT INTO" statement?
The column remains empty
The default value is used
An error is generated
The table is deleted
In the SQL statement "INSERT INTO groups (name) VALUES ('N3');", why is the "profile" column not specified?
It is an auto-incremented field
It has a default value
It is a primary key
It is a foreign key
An example of SQL "INSERT INTO" statements with explanations of column names and default values.
INSERT INTO students (id, name, age) VALUES (1, 'John', DEFAULT);
INSERT INTO students (id, name, age) VALUES ('John', 1, DEFAULT);
INSERT INTO students VALUES (1, 'John', DEFAULT);
INSERT INTO students (id, name, age) VALUES (DEFAULT, 'John', 1);
What is the purpose of the `INSERT INTO` statement in SQL?
To delete rows from a table
To update rows in a table
To add new rows to a table
To create a new table
Which of the following is true about auto-incremented fields in SQL when using the `INSERT INTO` statement?
Auto-incremented fields must always be specified in the `INSERT INTO` statement
Auto-incremented fields are automatically populated and do not need to be specified
Auto-incremented fields must be manually updated after insertion
Auto-incremented fields are optional but recommended to specify
In the example `INSERT INTO students (DOB, name, groupID) VALUES ("1997-02-11", "Jeff", 1);`, what does `groupID` represent?
The name of the student
The date of birth of the student
The group identifier for the student
The auto-incremented field for the student
What happens if you omit column names in the `INSERT INTO` statement, as shown in the example `INSERT INTO students VALUES ("", "Kate", "1997-09-22", 1);`?
The database will throw an error
The values will be inserted into the table in the order of the columns
The values will be ignored
The database will automatically assign default values to all columns
What is the correct format for inserting a date in MySQL?
DD-MM-YYYY without quotations
MM-DD-YYYY inside quotations
YY-MM-DD inside quotations
YYYY-DD-MM without quotations
Which data type in MySQL is used to insert the current date and time automatically?
Date
Timestamp
Time
Datetime
What does the following SQL query do: `SELECT * FROM groups;`?
Deletes all records from the 'groups' table
Selects all columns and rows from the 'groups' table
Updates all records in the 'groups' table
Creates a new table named 'groups'
In the provided example, what is the profile associated with groupID 5 in the 'groups' table?
Networking
IT
Multimedia
Computing
What is the studentID of the student named 'Mark' in the 'students' table?
1
4
5
6
What is the primary purpose of the SQL WHERE clause?
To insert data into a table
To filter or search data based on specified criteria
To delete data from a table
To create a new table
Which SQL statement correctly uses the WHERE clause to filter data?
SELECT column_name FROM table_name WHERE column_name operator value;
INSERT INTO table_name WHERE column_name operator value;
DELETE FROM table_name WHERE column_name operator value;
CREATE TABLE table_name WHERE column_name operator value;
In SQL, what does the WHERE clause help achieve when querying a database?
It sorts the data in ascending order
It extracts records that meet specific criteria
It combines multiple tables into one
It updates all records in a table
Which of the following is an example of an arithmetic operator in SQL?
AND
+
=
LIKE
Which operator is used in SQL for comparing two values to check if they are not equal?
!=
AND
%
LIKE
Which of the following is a logical operator in SQL?
BETWEEN
*
=
%
What type of operator is used to perform mathematical calculations in SQL?
Logical Operators
Arithmetic Operators
Comparison Operators
Aggregate Functions
Which of the following operators can be used to filter data based on a range in SQL?
BETWEEN
+
!=
%
What does the `WHERE` clause in SQL allow you to do when working with dates?
Filter data based on specific date conditions.
Create new tables based on date values.
Delete rows from a table based on date values.
Update date values in a table.
In the query `SELECT * FROM students WHERE year(DOB)='1997';`, what does the `year(DOB)` function do?
Extracts the year from the `DOB` column.
Converts the `DOB` column into a string.
Filters rows where the `DOB` column is null.
Adds a year to the `DOB` column.
Which of the following is true about comparing dates in SQL?
Dates can be compared as string values or numeric values.
Dates must always be compared as numeric values.
Dates cannot be compared directly in SQL.
Dates must be converted to integers before comparison.
What will the query `SELECT * FROM students WHERE month(DOB)='5';` return?
Rows where the month in the `DOB` column is May.
Rows where the year in the `DOB` column is 2005.
Rows where the day in the `DOB` column is the 5th.
Rows where the `DOB` column is null.
In the query `SELECT * FROM students WHERE day(DOB)>=10 AND day(DOB)<=15;`, what does the condition `day(DOB)>=10 AND day(DOB)<=15` do?
Filters rows where the day in the `DOB` column is between 10 and 15.
Filters rows where the month in the `DOB` column is October.
Filters rows where the year in the `DOB` column is 2015.
Filters rows where the `DOB` column is null.
What is the primary key in the "customers" table?
id
name
address
customer_id
Which column in the "orders" table is a foreign key?
order_id
customer_id
item
supplier
What is the price of the "Nexus-6P" item in the "items" table?
200
250
300
350
Which supplier provides the "Xbox-360" item?
Microsoft
Sony
Amazon
What is the phone number of the supplier "Sony"?
865-898-644
661-182-856
500-122-999
400-123-888
What does the BETWEEN operator in SQL do?
Selects values within a range, including numbers, text, or dates.
Selects values that match a specific pattern.
Selects values that are equal to a given value.
Selects values that are greater than a given value.
Which SQL query retrieves items with a price between 150 and 250?
SELECT * FROM ITEMS WHERE price BETWEEN 150 AND 250;
SELECT * FROM ITEMS WHERE price > 150 AND price < 250;
SELECT * FROM ITEMS WHERE price = 150 OR price = 250;
SELECT * FROM ITEMS WHERE price NOT BETWEEN 150 AND 250;
What is the result of the query: SELECT * FROM customers WHERE address BETWEEN "Banepa" AND "Kathmandu"?
It retrieves customers whose addresses are alphabetically between "Banepa" and "Kathmandu".
It retrieves customers whose addresses are exactly "Banepa" and "Kathmandu".
It retrieves customers whose addresses are not "Banepa" or "Kathmandu".
It retrieves customers whose addresses are alphabetically after "Kathmandu".
Which of the following data types can the BETWEEN operator work with in SQL?
Numbers, text, and dates.
Only numbers.
Only text.
Only dates.
What does the IN operator in SQL allow you to do?
Specify multiple values in a WHERE clause to perform membership checks.
Perform arithmetic operations in a SELECT statement.
Sort data in ascending or descending order.
Create new tables in a database.
Which SQL query retrieves all rows where the customer_id is 1, 2, or 3 using the IN operator?
SELECT * FROM orders WHERE customer_id IN (1,2,3);
SELECT * FROM orders WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 3;
SELECT * FROM orders WHERE customer_id BETWEEN 1 AND 3;
SELECT * FROM orders WHERE customer_id LIKE '1,2,3';
What is the result of the query "SELECT * FROM orders WHERE customer_id IN (1,2,3);" based on the provided table?
Rows with customer_id 1, 2, and 3 are retrieved.
Rows with customer_id 4 and 5 are retrieved.
All rows in the table are retrieved.
No rows are retrieved.
Which of the following is NOT a valid use case for the IN operator in SQL?
Checking membership of values in a WHERE clause.
Filtering rows based on multiple values.
Performing arithmetic operations.
Simplifying complex OR conditions.
What is the purpose of the LIKE operator in a WHERE clause in SQL?
To perform arithmetic operations
To search for a specified pattern
To insert data into a table
To delete data from a table
Which of the following patterns will match addresses starting with the letter "k" in SQL using the LIKE operator?
%k
k%
_k
%k%
Using the LIKE operator, what does the pattern "%u" match in SQL?
Any string containing the letter "u"
Strings starting with the letter "u"
Strings ending with the letter "u"
Strings with "u" as the second character
Which of the following SQL queries will retrieve rows where the address contains "it"?
SELECT * FROM customers WHERE address LIKE "it%";
SELECT * FROM customers WHERE address LIKE "%it%";
SELECT * FROM customers WHERE address LIKE "_it";
SELECT * FROM customers WHERE address LIKE "it";
Which aggregate function in SQL is used to calculate the sum of all values in a column?
MAX()
MIN()
SUM()
AVG()
What does the MAX() function in SQL return when applied to a column?
The sum of all values in the column
The maximum value in the column
The minimum value in the column
The average value in the column
If the prices of items in a table are 200, 300, 300, and 250, what will the result of the SUM(price) query be?
1050
950
1000
1100
Which aggregate function in SQL is used to calculate the average value of a column?
AVG()
SUM()
MIN()
MAX()
What is the purpose of the MIN() function in SQL?
To find the maximum value in a column
To find the minimum value in a column
To calculate the sum of all values in a column
To calculate the average value of a column
What is the result of the SQL query `SELECT 2 + 2;` in MariaDB?
2
4
0
6
What is the result of the SQL query `SELECT 2 - 2;` in MariaDB?
2
4
0
-2
What does the "AS" keyword in SQL do?
It performs arithmetic operations.
It is used to give a temporary column name.
It is used to delete a column.
It is used to create a new table.
What is the result of the SQL query "SELECT 9 / 3 AS quotient;"?
3.0000
0
9
1
What is the result of the SQL query "SELECT 9 % 3 AS remainder;"?
3
0
9
1
What is the purpose of the SQL query shown in the image?
To calculate the total price of all items.
To calculate the discounted price of each item.
To delete items from the database.
To update the supplier information for each item.
What arithmetic operation is used in the SQL query to calculate the discounted price?
Addition
Subtraction
Multiplication
Division
What does the expression `price - (price * 0.10)` in the SQL query represent?
The original price of the item.
The discounted price of the item.
The tax added to the price.
The supplier's profit margin.
How many rows are displayed in the result set after executing the SQL query?
5 rows
6 rows
7 rows
8 rows
Which SQL clause is used to rename the calculated column in the query?
WHERE
AS
SELECT
FROM
What is the main purpose of a data dictionary?
To store actual data in a database
To provide metadata or information about data
To organize data in a hierarchical structure
To enforce constraints on database tables
How is a data dictionary typically organized?
In a hierarchical tree structure
In a spreadsheet format
In a graphical user interface
In a textual document
What type of information does a data dictionary include about tables and fields?
Information about database users
Information about data type and field length
Information about database security
Information about database backups
Which of the following statements is true about the layout or information in a data dictionary?
It follows a strict standard format
It has no one set standard in terms of layout or information
It is always organized alphabetically
It is always organized by data type
What does a data dictionary technically represent?
A database about a database
A collection of database queries
A list of database users
A backup of database tables
What is the purpose of the "ID" column in the "Employee" entity of the data dictionary?
To store the name of the employee
To uniquely identify each employee
To reference the department ID
To store the length of the employee's name
Which data type is used for the "ID" column in the "Employee" entity?
VARCHAR
INT
CHAR
TEXT
What does the "Auto incremented" note in the "ID" column signify?
The value of the column is manually entered
The value of the column automatically increases for each new record
The column is used as a foreign key
The column is nullable
Which column in the "Employee" entity is marked as a foreign key?
ID
Name
Department
Notes
What is the length of the "Name" column in the "Employee" entity?
32
255
128
64
