D426 Operators/Code/Queries

What is the function of the unary - operator in SQL?

It subtracts one numeric value from another.

It returns the absolute value of a number.

It is used for string concatenation.

It reverses the sign of one numeric value.

It reverses the sign of one numeric value. i.e -5

Subtracting one numeric value from another is binary, not unary.

Assume the second Foreign Key is the last line of code in the CREATE TABLE statement. Which syntax is correct? Pay attention to the commas and parenthesis:

FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE, FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL

FOREIGN KEY (HorseID) REFERENCES Horse(ID), ON DELETE CASCADE, FOREIGN KEY (StudentID) REFERENCES Student(ID), ON DELETE SET NULL,

FOREIGN KEY HorseID REFERENCES Horse(ID) ON DELETE CASCADE, FOREIGN KEY StudentID REFERENCES Student(ID) ON DELETE SET NULL

FOREIGN KEY HorseID REFERENCES Horse(ID), ON DELETE CASCADE, FOREIGN KEY StudentID REFERENCES Student(ID) ON DELETE SET NULL

FOREIGN KEY (HorseID) REFERENCES Horse(ID)
    ON DELETE CASCADE,
FOREIGN KEY (StudentID) REFERENCES Student(ID)
    ON DELETE SET NULL

Which SELECT statement would return movies with the word ‘star’ in the title?

A) SELECT * FROM Movie WHERE Title LIKE '%star%';

B) SELECT * FROM Movie WHERE Title = '%star%';

C) SELECT * FROM Movie WHERE '%star%' IN Title;

D) SELECT * FROM Movie WHERE Title IN '%star%';

A) SELECT * FROM Movie WHERE Title LIKE '%star%';

Write a SELECT statement that selects the Genre and most recent ReleaseYear from table Song for each genre. Use a HAVING clause to select only genre groups that have more than one row count.

SELECT Genre, MAX(ReleaseYear)
FROM Song
GROUP BY Genre
HAVING (COUNT(Genre) > 1);

“for each genre” = GROUP BY Genre

Use MAX() to find the biggest number in a column (in this scenario).

Use COUNT() to count rows in a column.

Using the YEAR() and MONTH() functions, create a SELECT statement to select movies where ReleaseDate is after 2017 or in November from table Movie

SELECT * FROM Movie

WHERE YEAR(ReleaseDate) > 2017 OR MONTH(ReleaseDate) = 11;

2017 nor 11 are in quotations.

What data type is an integer with range 0 to 16,777,215

MEDIUMINT

The Horse table has the following columns: ID - integer, auto increment, primary key RegisteredName - variable-length string Breed - variable-length string Height - decimal number BirthDate - date

Delete the following rows: Horse with ID 5 All horses with breed Holsteiner or Paint All horses born before March 13, 2013

DELETE FROM Horse 
WHERE ID = 5 OR 
   Breed = 'Holsteiner' OR 
   Breed = 'Paint' OR 
   BirthDate < '2013-03-13';

Height - number with 3 significant digits and 1 decimal place, must be ≥ 10.0 and ≤ 20.0

Which is proper syntax? Select all that apply:

A) Height DEC(3,1) CHECK (Height >= 10 AND Height <= 20)

B) Height DEC(3,1) CHECK (Height >= 10 AND <= 20)

C) Height DEC(3,1) CHECK (Height BETWEEN 10 AND 20)

A) Height DEC(3,1) CHECK (Height >= 10 AND Height <= 20)

And

C) Height DEC(3,1) CHECK (Height BETWEEN 10 AND 20)

What is the proper syntax for creating a table with this column?:

Breed - variable-length string with max 20 characters, must be one of the following: Egyptian Arab, Holsteiner, Quarter Horse, Paint, Saddlebred

Breed VARCHAR(20) CHECK (Breed IN ('Egyptian Arab', 'Holsteiner', 'Paint', 'Quarter Horse', 'Saddlebred'))

Which wildcard in LIKE matches exactly one character?

A. %

B. *

C. _

D. #

C. _ In SQL LIKE, underscore (_) matches exactly one character; % matches any number of characters.

What does an UPDATE statement look like for ONE value?

UPDATE TableName          UPDATE TableName
SET column = value        SET column = value,
WHERE condition;          WHERE condition;

UPDATE TableName

SET column = value

WHERE condition;

NO COMMA AT THE END OF SET STATEMENT

LIKE BINARY ‘%E%’;

What does the BINARY do?

BINARY makes the pattern matching case-sensitive. An example matching word would be ‘English’ (because of the capital E).

For A, add a NOT NULL constraint to an existing column Salary in the Department table.

ALTER TABLE Department
___A___ Salary INT NOT NULL;

A) ADD CONSTRAINT Salary INT NOT NULL;

B) CHANGE Salary INT NOT NULL;

C) UPDATE Salary INT NOT NULL;

B) CHANGE Salary INT NOT NULL;

Write ALTER statements to make the following modifications to Movie:

1. Add a Producer column with VARCHAR data type (max 50 chars).

2. Remove the Genre column.

3. Change the Year column's name to ReleaseYear, and change the data type to SMALLINT.

ALTER TABLE Movie

ADD COLUMN Producer VARCHAR(50),

DROP COLUMN Genre,

CHANGE COLUMN Year ReleaseYear SMALLINT;

Adding COLUMN is not necessary.

Which SQL statement removes specific rows from a table?

ERASE

DELETE

REMOVE

DROP

DELETE

❌ DROP is for tables/databases/columns

❌ REMOVE is not a SQL command

❌ ERASE is not a SQL command

Which numeric data type is approximate and uses floating point?

A. DECIMAL

B. INT

C. FLOAT

D. DATE

C. FLOAT. FLOAT is an approximate floating-point numeric type; DECIMAL is exact fixed-point, INT is integer, DATE is temporal.

To store the number of employees in a small company (which will not exceed 30,000), which integer data type offers the best storage efficiency?

TINYINT

SMALLINT

INT

BIGINT

SMALLINT

TINYINT

-128 to 127

SMALLINT

-32,768 to 32,767

Which function returns the current date and time in many SQL implementations?

A. MOMENT()

B. DATE_ADD()

C. CURRENT_TIMESTAMP (or NOW())

D. TODAY()

C. CURRENT_TIMESTAMP (or NOW())

NOW() (and CURRENT_TIMESTAMP) return current date/time in many SQL dialects; DATE_ADD is for arithmetic, TODAY() is not standard SQL.

To store a 5-digit US ZIP code like '90210', which data type provides the most efficient and appropriate storage? 

INT 

VARCHAR(5)

CHAR(5)

DECIMAL(5,0)

CHAR(5)

❌ INT will remove leading zeros (like for zipcodes like 07356)

❌ DECIMAL will remove leading zeros

❌ VARCHAR(5) will allow any length of input from 1-5, when we want exactly 5.

If you need to store a variable-length name up to 100 characters, which type is most appropriate?

A. CHAR(100)

B. VARCHAR(100)

C. TEXT(100)

D. BLOB(100)

B. VARCHAR(100)

VARCHAR is variable-length up to the max and is more storage-efficient for varying-length names than fixed CHAR(100); TEXT is overkill.

ROUND(n,d) returns ___

Returns n rounded to d decimal places

REPLACE(s, from, to) Returns the string s with all occurrences of from replaced with to.

SELECT REPLACE('This and that', 'and', 'or');

Returns ___

Returns ‘This or that’

SUBSTRING(s, pos, len) Returns the substring from s that starts at position pos and has length len.

SELECT SUBSTRING('Boomerang', 1, 4);

Returns ___

‘Boom’. String positions in SQL start at 1. So B is position 1.

CURDATE(); returns the current date, CURTIME(); returns the current time, and ___ returns the current date AND time

NOW();

Which data type would you choose to store an exact monetary value with two decimal places?

A. FLOAT

B. DECIMAL(10,2)

C. VARCHAR(10)

D. DOUBLE

B. DECIMAL(10,2). DECIMAL gives exact fixed-point precision needed for money; FLOAT and DOUBLE are approximate, and VARCHAR is textual.

Which data type is fixed-length and pads unused characters?

A. VARCHAR(N)

B. TEXT

C. CHAR(N)

D. BLOB

C. CHAR(N). CHAR is fixed-length and pads unused characters; VARCHAR is variable length, TEXT is for larger variable-length data, and BLOB is binary.

Which data type is best for storing large binary objects like images?

A. TEXT

B. BLOB

C. CHAR

D. DATE

B. BLOB. BLOB stores binary large objects, such as images; TEXT is for large text, CHAR/VARCHAR for short strings, DATE for dates.

What does the DISTINCT clause do in a SELECT?

A. Removes NULL values from results

B. Returns only unique values for the selected expressions

C. Orders the result set uniquely

D. Limits rows returned to 100

B. Returns only unique values for the selected expressions. DISTINCT removes duplicate result rows for the selected expressions; it does not remove NULLs specifically, nor order or limit rows.

LOG(n) returns ___

Returns the natural logarithm of n

What is square root?

A value that, when multiplied by itself, gives you the original number. The square root of 9 is 3, because 3×3=9.

ABS(n) returns ___

The absolute value of n

What is absolute value?

Absolute value is the distance a number is from zero on the number line. It is always positive.

According to standard operator precedence, which of the following operators is evaluated before AND but after comparison operators like = or >?

<

+ (binary)

NOT

*

NOT

Common SQL operator precedence order is:

1. *, / (multiplication, division)

2. +, - (addition, subtraction)

3. Comparison operators (=, >, <, >=, <=, <>)

4. NOT

5. AND

6. OR

What is the function of the unary - operator in SQL?

It subtracts one numeric value from another.

It returns the absolute value of a number.

It is used for string concatenation.

It reverses the sign of one numeric value.

It reverses the sign of one numeric value. i.e -5

Subtracting one numeric value from another is binary, not unary. It involves 2 numbers (binary).

Which of the following comparison operators is used to check for inequality in SQL?

~=

!==

<>

=!

<>

❌ =! is invalid syntax.

!= would be correct, but is not listed here. <> is the same thing. It just means greater than or less than the value next to it, which is the same as “not this value”.

What would it look like to add a Foreign Key constraint to a table that references another table?

FOREIGN KEY (ColumnName) REFERENCES TableName (ColumnName)

For A, name the primary key table constraint 'DepartmentKey'.

CREATE TABLE Department (
   Code INT,
   Name VARCHAR(20),
   ManagerID INT,
   __A__ PRIMARY KEY (Code)
;

A) CONSTRAINT DepartmentKey PRIMARY KEY (Code)

B) CONSTRAINT ‘DepartmentKey’ PRIMARY KEY (Code)

C) CONSTRAINT (DepartmentKey) PRIMARY KEY (Code)

D) CONSTRAINT (‘DepartmentKey’) PRIMARY KEY (Code)

A) CONSTRAINT DepartmentKey PRIMARY KEY (Code)

Constraints do not need parenthesis like Primary Keys do.

For A, add a UNIQUE constraint called UniqueNameMgr that ensures the Name and ManagerID combination are unique.

CREATE TABLE Department (
   Code TINYINT UNSIGNED,
   Name VARCHAR(20),
   ManagerID SMALLINT,
   _____________A_____________,
   PRIMARY KEY (Code)
   ;

CONSTRAINT UniqueNameMgr UNIQUE (Name, ManagerID),

What does an INSERT statement look like? For example, insert 3 movie titles, their ratings, and their release date into a table called Movie.

INSERT INTO Movie (Title, Rating, ReleaseDate) VALUES

(‘Donnie Darko’, ‘R’,’2001-10-26’),

(‘Alita: Battle Angel’, ‘PG-13’, ‘2019-02-13’),

(‘The Labyrinth’, ‘PG-13’, ‘1986-06-27’);

What does an UPDATE statement look like?

UPDATE TableName

SET column=value, column=value etc (NO COMMA AT THE END)

WHERE condition;

Which data type is a decimal number with variable precision and 4 bytes of storage?

FLOAT

The ___ operator is either unary or binary arithmetic operator

-

The - operator usually has two numeric operands. Ex: 5.4 - 3.0. Occasionally, the - operator changes the sign of a single numeric operand. Ex: -DegreesCentigrade.

The ___ operator is a binary arithmetic operator

%

The % operator returns the remainder of one operand divided by another. The remainder is converted to an integer. The % operator is pronounced 'modulo'.

The ___ operator is a binary comparison operator

!=

!= compares two operands of the same data type, and returns TRUE if the operands are different values. The <> operator is equivalent to !=, and most databases support both operators.

The ___ operator is a unary logical operator

NOT

NOT takes a single (unary) logical operand. NOT TRUE returns FALSE. NOT FALSE returns TRUE.

The ___ is a binary logical operator

AND

AND takes two (binary) logical operands and returns one logical value. AND returns TRUE when both operands are TRUE. AND returns FALSE when either operand is FALSE.

Which SQL operator is used to match a value against a list of possible values?

A. BETWEEN

B. LIKE

C. IN

D. EXISTS

C. IN.

IN checks whether a value matches any value in a list, while BETWEEN checks a range, LIKE checks patterns, and EXISTS tests for rows returned by a subquery.

Which operator returns TRUE only when both operands are TRUE?

A. OR

B. NOT

C. AND

D. BETWEEN

C. AND.

AND returns TRUE only when both operands are TRUE; OR returns TRUE if either is TRUE and NOT negates.

Which of the following is a comparison operator?

A. CONCAT

B. !=

C. ROUND

D. GROUP BY

B. !=

!= (or <>) is a comparison operator for inequality; CONCAT and ROUND are functions, GROUP BY is a clause.

Which operator is used to negate a logical expression?

A. NOT

B. !=

C. <>

D. IS NOT

A. NOT

NOT negates a boolean expression; != and <> are inequality operators while IS NOT is used in specific contexts (e.g., IS NOT NULL).

Which SQL logical operator returns FALSE only when both operands are FALSE?

A. AND

B. OR

C. XOR

D. NOT

B. OR.

OR returns FALSE only when both operands are FALSE; AND returns TRUE only when both are TRUE, NOT negates.

A relational operator that allows for the combination of information from two or more tables is known as the ____ operator.

A. SELECT
B. PROJECT
C. JOIN
D. DIFFERENCE

C. The Join clause facilitates the connection of two table through identification of a common attribute.

The LIKE operator is used for ____.

A. BETWEEN
B. IS NULL
C. pattern matching
D. IN

C. The LIKE operator combined with wildcard characters can evaluate string values.

Which statement will remove all rows from the Materials table that have a Status value of ‘Obsolete’ but do not have a value for the VendorID column?

A. DELETE Materials WHERE Status = ‘Obsolete’ OR VendorID IS NULL

B. DELETE FROM Materials WHERE Status = ‘Obsolete’ AND VendorID IS NULL

C. DELETE MaterialID, Description, Status, VendorID FROM Materials WHERE Status = ‘Obsolete’ AND VendorID IS NULL

D. DELETE FROM Materials WHERE Status = ‘Obsolete’ WHERE VendorID IS NULL

B. When referring to cells that contain no data the proper operator is IS NULL.

UPDATE tablename

__________________

[WHERE conditionlist];

A. SET columnname = expression
B. columnname = expression
C. expression = columnname
D. LET columnname = expression

A. UPDATE is the Data Definition Language statement to change data in a table and requires a SET statement to identify new values.

What is the command to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the value of V_CODE match?

A.SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE <> VENDOR.V_CODE;

B.SELECT " " FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

C.SELECT " " FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE;

D.SELECT " "

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE => VENDOR.V_CODE;

B. Retrieving matching fields requires an = operator.

Which query selects V_CODE = 21344 OR V_CODE = 24288?

A. SELECT … WHERE V_CODE = 21344 OR V_CODE <= 24288

B. SELECT … WHERE V_CODE = 21344 OR V_CODE => 24288

C. SELECT … WHERE V_CODE = 21344 OR V_CODE > 24288

D. SELECT … WHERE V_CODE = 21344 OR V_CODE = 24288

D. The proper syntax in this question is a combination of both conditions using an OR operator.

Which query will list all rows on or after January 20, 2006?

A. … >= '2006-01-20'
B. … >= #01/20/2004#
C. … >= '20-JAN-2004'
D. … >= {01-20-2004}

A. Single quotes are required on dates as the – symbol is a reserved operator for arithmetic functions.

Which query will output the table contents when V_CODE <= 21344?

A. SELECT … WHERE V_CODE <> 21344;

B. SELECT … WHERE V_CODE <= 21344;

C. SELECT … WHERE V_CODE => 21344;

D. SELECT … WHERE V_CODE = 21344;

B. The proper syntax to select the specific values in this question is WHERE V_CODE <=21344

Which command is used to select partial table contents?

A. SELECT <column(s)> FROM <Table name> WHERE <Item>;

B. LIST <column(s)> FROM <Table name> WHERE <Conditions>;

C. SELECT <column(s)> FROM <Table name> WHERE <Conditions>;

D. LIST<column(s)> FROM <Table name> WHERE <Item>;

C.

Which command would be used to delete the table row where the P_Code = '2238/QPD'?

A. DELETE FROM PRODUCT WHERE P_CODE = '2238/QPD';

B. REMOVE FROM PRODUCT WHERE P_CODE = '2238/QPD';

C. ERASE FROM PRODUCT WHERE P_CODE = '2238/QPD';

D. ROLLBACK FROM PRODUCT WHERE P_CODE = '2238/QPD';

A.

Which command would you use when making corrections to the PRODUCT table?

A. CHANGE PRODUCT …
B. ROLLBACK PRODUCT …
C. EDIT PRODUCT …
D. UPDATE PRODUCT …

D

To list all the contents of the PRODUCT table, you would use ____.

A. LIST * FROM PRODUCT;
B. SELECT * FROM PRODUCT;
C. DISPLAY * FROM PRODUCT;
D. SELECT ALL FROM PRODUCT;

B. Using the wildcard character * in a SELECT statement returns all values that satisfy the conditions of the statement.

The SQL command that enables you to make changes in the data is ____.

A. INSERT
B. SELECT
C. COMMIT
D. UPDATE

D. UPDATE is the Data Manipulation Language (DML) clause to modify data in a table.

The SQL command that lets you insert data into a table, one row at a time, is ____.

A. INSERT
B. SELECT
C. COMMIT
D. UPDATE

A. INSERT INTO is the Data Manipulation Language (DML) clause to add data to a table.

DISTINCT filters the results to remove duplicates. ORDER BY ____.

A. does the same thing
B. alters the order of the rows in a table
C. modifies the presentation by changing the order of the result set
D. removes duplicates in the table

C. ORDER BY applies sorting to your statement’s output.

Which tool/statement helps you see how the DBMS will execute a query?

A. EXPLAIN statement

B. DESCRIBE statement

C. SHOW PLAN ONLY

D. ANALYZE TABLE

A. EXPLAIN statement. EXPLAIN shows the query execution plan, including index usage and estimated rows.

Which referential integrity action sets invalid foreign keys to NULL when the referenced primary key is deleted?

A. RESTRICT

B. SET NULL

C. CASCADE

D. SET DEFAULT

B. SET NULL. SET NULL makes foreign key columns NULL on parent deletion if allowed; RESTRICT rejects, CASCADE deletes children.

What happens with ON DELETE CASCADE on a parent table when a parent row is deleted?

A. Child rows with matching foreign keys are automatically deleted.

B. Deletion is rejected.

C. Child foreign keys are set to default.

D. Nothing — it’s ignored.

A. Child rows with matching foreign keys are automatically deleted. ON DELETE CASCADE removes dependent child rows automatically; RESTRICT would reject deletion.

Which command shows the CREATE TABLE statement for an existing table (in MySQL)?

A. SHOW CREATE TABLE table_name;

B. DESCRIBE CREATE TABLE table_name;

C. SHOW SCHEMA table_name;

D. GET CREATE TABLE table_name

A. SHOW CREATE TABLE table_name; MySQL's SHOW CREATE TABLE displays the CREATE statement; DESCRIBE shows columns, not the CREATE.

To prevent a column from accepting values outside a range, you would use:

A. NOT NULL

B. CHECK (col BETWEEN low AND high)

C. UNIQUE

D. DEFAULT

B. CHECK (col BETWEEN low AND high) CHECK with an expression like BETWEEN enforces range constraints; NOT NULL and UNIQUE do different jobs.

Which clause would you use to enforce an action when a referenced primary key is deleted?

A. ON DELETE CASCADE

B. ON DELETE IGNORE

C. ON CONSTRAINT DELETE

D. ON DROP CASCADE

A. ON DELETE CASCADE

ON DELETE CASCADE specifies child-row deletion when the parent is deleted; the others are not standard referential actions.

Which DCL command grants privileges to a user?

A. GRANT

B. ALLOW

C. PROVIDE

D. PERMIT

A. GRANT GRANT assigns privileges; ALLOW/PROVIDE/PERMIT are not standard SQL privilege commands.

Which statement will remove a column named temp from table t?

A. ALTER TABLE t DROP COLUMN temp;

B. DELETE COLUMN temp FROM t;

C. ALTER TABLE t REMOVE temp;

D. DROP COLUMN temp ON t

A. ALTER TABLE t DROP COLUMN temp; Standard syntax to remove a column; other options are invalid.

Which constraint can specify an expression that must be TRUE for each row?

A. UNIQUE

B. CHECK

C. DEFAULT

D. FOREIGN KEY

B. CHECK

CHECK enforces that an expression evaluates TRUE for each row; UNIQUE and NOT NULL are different rules, DEFAULT supplies values.

Which SQL statement would rename a column in MySQL from oldname to newname while preserving type?

A. ALTER TABLE t NEWNAME COLUMN oldname TO newname;

B. ALTER TABLE t CHANGE oldname newname VARCHAR(100);

C. UPDATE TABLE t RENAME oldname newname;

D. ALTER TABLE t MODIFY oldname TO newname

B. ALTER TABLE t CHANGE oldname newname VARCHAR(100); In MySQL, CHANGE lets you rename and re-specify the type; some DBMS also support RENAME COLUMN syntax (A) but B is the classic MySQL form.

Which clause is used with CREATE VIEW to ensure inserts/updates satisfy the view definition?

A. WITH CHECK OPTION

B. WITH READ ONLY

C. WITH VIEW PROTECTION

D. WITH VALIDATION

A. WITH CHECK OPTION

WITH CHECK OPTION causes updates/inserts through the view to be rejected if they would violate the view's WHERE clause; other options are not standard or are different.

What does DEFAULT 'N/A' do in a column definition?

A. Makes the column NOT NULL.

B. Sets the default value inserted when no value is provided.

C. Rejects inserts without this column.

D. Converts NULLs to 'N/A' at SELECT time only.

B. Sets the default value inserted when no value is provided. DEFAULT defines what value a column receives when omitted from an INSERT; it does not auto-make NOT NULL or reject inserts.

Which statement will drop a named foreign key constraint fk_order_customer?

A. DROP FOREIGN KEY fk_order_customer;

B. ALTER TABLE orders DROP FOREIGN KEY fk_order_customer;

C. DROP CONSTRAINT fk_order_customer ON orders;

D. DELETE CONSTRAINT fk_order_customer FROM orders

B. ALTER TABLE orders DROP FOREIGN KEY fk_order_customer; Dropping a named foreign key uses ALTER TABLE … DROP FOREIGN KEY in MySQL; plain DROP FOREIGN KEY is incomplete.

How do you define a foreign key referencing customers(id) in a CREATE TABLE statement?

A. FOREIGN KEY (customer_id) REFERENCES customers(id)

B. KEY (customer_id) REFERENCES customers.id

C. REFERENCES customers(customer_id)

D. FOREIGN (customer_id) TO customers(id)

A. FOREIGN KEY (customer_id) REFERENCES customers(id) This is the standard FOREIGN KEY … REFERENCES syntax; the others are incorrect forms.

Which constraint ensures column values are unique across rows?

A. PRIMARY KEY only

B. UNIQUE

C. NOT NULL

D. CHECK

B. UNIQUE. UNIQUE enforces distinct values; PRIMARY KEY also enforces uniqueness and non-null, but UNIQUE alone is the dedicated uniqueness constraint.

Which of the following defines a PRIMARY KEY on two columns order_id and product_id?

A. PRIMARY KEY order_id, product_id

B. PRIMARY KEY (order_id, product_id)

C. UNIQUE (order_id, product_id) PRIMARY

D. KEY PRIMARY (order_id product_id)

B. PRIMARY KEY (order_id, product_id) Composite primary keys are declared with parentheses; other choices are syntactically wrong.

How do you add a new column email VARCHAR(255) to an existing users table?

A. ALTER TABLE users ADD email VARCHAR(255);

B. MODIFY TABLE users ADD COLUMN email VARCHAR(255);

C. UPDATE TABLE users ADD email VARCHAR(255);

D. ALTER users ADD email VARCHAR(255)

A. ALTER TABLE users ADD email VARCHAR(255); Standard ALTER TABLE ADD syntax; other options are not valid SQL in mainstream RDBMS.

Which constraint prevents a column from containing NULL?

A. UNIQUE

B. DEFAULT

C. NOT NULL

D. CHECK

C. NOT NULL. NOT NULL enforces non-null values; UNIQUE ensures distinctness but allows NULLs (depending on DB), DEFAULT supplies values, CHECK enforces expressions.

Which command removes all rows from a table but may be implemented differently from DELETE?

A. DROP TABLE

B. TRUNCATE TABLE

C. REMOVE ALL FROM table

D. DELETE ALL

B. TRUNCATE TABLE. TRUNCATE removes all rows quickly (often with different internal behavior from DELETE) while DROP removes the table itself.

What happens if you execute UPDATE employees SET salary = salary * 1.05; with no WHERE clause?

A. Only salaries greater than 0 are updated.

B. All rows in employees will have salary multiplied by 1.05.

C. The statement fails because WHERE is required.

D. Only the first row is updated.

B. All rows in employees will have salary multiplied by 1.05. Omitting WHERE updates every row; WHERE is optional and needed to restrict which rows are updated.

Which statement creates a new table named customer with columns id INT PRIMARY KEY and name VARCHAR(100)?

A. CREATE customer (id INT PRIMARY KEY, name VARCHAR(100));

B. CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(100));

C. MAKE TABLE customer id INT PRIMARY KEY, name VARCHAR(100);

D. CREATE TABLE customer id INT PRIMARY KEY, name VARCHAR(100);

B. CREATE TABLE customer (id INT PRIMARY KEY, name VARCHAR(100)); Correct CREATE TABLE syntax; options A and D use incorrect syntax and C is not valid SQL.

Which clause filters groups produced by GROUP BY?

A. WHERE

B. HAVING

C. ORDER BY

D. DISTINCT

B. HAVING. HAVING filters groups produced by GROUP BY; WHERE filters rows before grouping.

Which SQL clause restricts rows returned from a SELECT using a logical condition?

A. GROUP BY

B. HAVING

C. WHERE

D. ORDER BY

C. WHERE. WHERE filters rows based on conditions before grouping or aggregation; HAVING filters groups, ORDER BY sorts.

The SQL expression salary BETWEEN 30000 AND 60000 is equivalent to:

A. salary > 30000 AND salary < 60000

B. salary >= 30000 AND salary <= 60000

C. salary > 30000 OR salary < 60000

D. salary = 30000 OR salary = 60000

B. salary >= 30000 AND salary <= 60000. BETWEEN is inclusive, equivalent to >= and <=; option A is exclusive, C/ D are incorrect forms.

To ensure that every product has a unique SKU (Stock Keeping Unit), a developer needs to add a constraint to the existing Products table. Which syntax correctly adds this table-level constraint?

ALTER TABLE Products SET UNIQUE (SKU); 

ALTER TABLE Products CREATE CONSTRAINT UNIQUE (SKU); 

ALTER TABLE Products ADD CONSTRAINT UQ_Product_SKU UNIQUE (SKU);

MODIFY TABLE Products ADD UNIQUE (SKU) AS UQ_Product_SKU; 

ALTER TABLE Products ADD CONSTRAINT UQ_Product_SKU UNIQUE (SKU);

❌CREATE CONSTRAINT is not a thing.

❌ MODIFY is for changing data types (int → char, etc)

❌ SET UNIQUE is not a thing.

A developer wants to give a name to a new constraint to make error messages more understandable. Which keyword allows this? 

NAME 

LABEL

CONSTRAINT

ALIAS

CONSTRAINT

A developer needs to clear all data from a very large LogEvents table containing millions of rows. The table structure must remain, but all records should be removed. Which command is typically the most efficient for this specific task because it is a minimally logged operation? 

DELETE ALL FROM LogEvents; 

DROP TABLE LogEvents; 

TRUNCATE TABLE LogEvents;

ALTER TABLE LogEvents CLEAR DATA;

TRUNCATE TABLE LogEvents;

TRUNCATE TABLE is designed to remove all rows from a table quickly and efficiently, especially in large tables.

❌ ALTER changes table structure (removing or adding columns), not removes data from the table

❌ DROP is for deleting entire tables/databases, not data from rows/columns

❌ DELETE ALL is not a valid SQL command. There is only DELETE FROM

A manager decides to discontinue all products in the 'Electronics' category. Which statement correctly removes all of these products from the Products table?

DROP FROM Products WHERE Category = 'Electronics';

DELETE FROM Products WHERE Category = 'Electronics';

REMOVE FROM Products WHERE Category = 'Electronics';

TRUNCATE Products WHERE Category = 'Electronics';

DELETE FROM Products WHERE Category = 'Electronics';

❌ DROP is for removing entire tables/databases.

❌ TRUNCATE removes all rows, and it cannot use a WHERE clause

❌ REMOVE is not an actual SQL command

Which statement correctly modifies the price of the product with ID 5 to $99.99 in the Products table?

MODIFY Products SET Price = 99.99 WHERE ID = 5;

UPDATE Products SET Price = 99.99 WHERE ID = 5;

CHANGE Products SET Price = 99.99 FOR ID = 5;

UPDATE Products (Price) VALUES (99.99) WHERE ID = 5;

UPDATE Products SET Price = 99.99 WHERE ID = 5;

Remember MODIFY is for changing a data TYPE, not for changing the actual data. UPDATE changes the data of an existing value.

❌ CHANGE is not a SQL command.

A developer needs to add a HireDate column to an existing Employees table. Which command should they use?

CREATE COLUMN HireDate IN Employees;

MODIFY TABLE Employees ADD HireDate DATE;

ALTER TABLE Employees ADD HireDate DATE;

UPDATE TABLE Employees ADD HireDate DATE;

ALTER TABLE Employees ADD HireDate DATE;

You don’t use CREATE for an already existing table. MODIFY is for changing data TYPES, not table structure. UPDATE is for changing the data itself, not table structure.

A developer wants to see the column definitions for a table named Employees. Which command would provide this information?

LIST COLUMNS FROM Employees;

SHOW Employees COLUMNS;

SHOW COLUMNS FROM Employees;

DESCRIBE TABLE Employees SHOW COLUMNS;

SHOW COLUMNS FROM Employees;

LIST is not a valid SQL command anywhere.

Before running a script that creates several tables, a developer wants to ensure they are working within the correct database. Which command should they use to select SalesDB as the default database?

SELECT DATABASE SalesDB;

DEFAULT DATABASE SalesDB;

USE SalesDB;

OPEN DATABASE SalesDB;

USE SalesDB;

The USE command sets the current working database so all subsequent SQL commands apply to it.

A database administrator needs to completely remove a temporary database named TempData. Which command should they use?

DELETE DATABASE TempData;

REMOVE DATABASE TempData;

DROP DATABASE TempData;

TRUNCATE DATABASE TempData;

DROP DATABASE TempData;

DELETE only works on rows in a table, not databases.

Which SQL clause is processed before results are grouped for aggregate functions?

WHERE

HAVING

ORDER BY

SELECT

WHERE

❌ORDER BY happens at the very end

❌HAVING happens after grouping (not before)