1/152
Looks like no tags are added yet.
What SQL statement creates a new database?
CREATE DATABASE database_name;
How do you select a database to work with?
USE database_name;
What SQL statement creates a table?
CREATE TABLE table_name (…);
How do you define a column as an integer primary key?
column_name INT PRIMARY KEY;
How do you define a VARCHAR column with a maximum length?
column_name VARCHAR(length);
How do you define a decimal column with 4 total digits and 2 decimal places?
column_name DECIMAL(4,2);
How do you define an integer column with a default value of 0?
column_name INT DEFAULT 0;
What SQL statement adds a new row to a table?
INSERT INTO table_name VALUES (value1, value2, …);
What is the syntax for inserting values into specific columns?
INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);
How do you insert multiple rows into a table?
INSERT INTO table_name VALUES (value1, value2, …), (value1, value2, …);
What must be true about inserted values and the table columns?
Their data types must match and the values must satisfy the table's constraints.
What SQL statement modifies existing data in a table?
UPDATE table_name SET column1 = value1 WHERE condition;
What keyword specifies the values to change in an UPDATE statement?
SET
What keyword limits which rows are modified by UPDATE?
WHERE
What SQL statement removes existing rows from a table?
DELETE FROM table_name WHERE condition;
What SQL statement retrieves data from a table?
SELECT
How do you retrieve every column and every row from a table?
SELECT * FROM table_name;
How do you retrieve specific columns from a table?
SELECT column1, column2, … FROM table_name;
How do you retrieve specific rows from a table?
SELECT * FROM table_name WHERE condition;
How do you retrieve specific columns from specific rows?
SELECT column1, column2, … FROM table_name WHERE condition;
What does SELECT DISTINCT do?
It returns unique values and removes duplicates from the result.
What is the syntax for retrieving unique values from one column?
SELECT DISTINCT column_name FROM table_name;
How do you retrieve unique combinations of two columns?
SELECT DISTINCT column1, column2 FROM table_name;
How do you count distinct values in a column?
SELECT COUNT(DISTINCT column_name) FROM table_name;
How do you count distinct values that meet a condition?
SELECT COUNT(DISTINCT column_name) FROM table_name WHERE condition;
What clause filters rows based on a condition?
WHERE
How do you filter for a numeric value less than 40?
WHERE unit_price < 40.00
How do you filter for a numeric value greater than 40?
WHERE unit_price > 40.00
How do you filter for a value less than or equal to 40?
WHERE unit_price <= 40.00
How do you test whether a value is exactly equal to another value?
WHERE column_name = value
How do you compare a text value in a WHERE clause?
WHERE column_name = 'text'
What comparison operator means not equal?
What does ORDER BY do?
It sorts the rows in the result.
How do you sort by one column?
ORDER BY column_name;
How do you sort by multiple columns?
ORDER BY column1, column2;
How do you sort a column in ascending order?
ORDER BY column_name ASC;
How do you sort a column in descending order?
ORDER BY column_name DESC;
How do you sort unique category and price combinations?
SELECT DISTINCT category, unit_price FROM products ORDER BY category, unit_price;
What does AS do in a SELECT statement?
It gives a column or calculation an alias.
How do you alias a calculated column?
SELECT column1 * column2 AS alias_name FROM table_name;
How do you add two values in a SQL calculation?
column_name + value
How do you subtract one value from another in SQL?
column_name
How do you multiply two column values in SQL?
column1 * column2
How do you divide a column value by a number?
column_name / number
What does the % operator do in SQL?
It returns the remainder after division.
How do you calculate inventory after adding 10 units?
stock_quantity + 10
How do you calculate inventory after selling 5 units?
stock_quantity
How do you calculate total inventory value?
unit_price * stock_quantity
How do you calculate half of a product's price?
unit_price / 2
How do you calculate the remainder after packing stock into boxes of 10?
stock_quantity % 10
How do you calculate a promotional price that is 0.50 lower?
unit_price
How do you display a calculation with a custom column name?
SELECT calculation AS alias_name FROM table_name;
How do you update a numeric column by adding 5 to its current value?
UPDATE table_name SET column_name = column_name + 5 WHERE condition;
How do you update only rows with a specific text status?
UPDATE table_name SET column_name = value WHERE status = 'status_value';
How do you add a new column to an existing table?
ALTER TABLE table_name ADD COLUMN column_name data_type;
How do you modify an existing column's data type?
ALTER TABLE table_name MODIFY COLUMN column_name data_type;
How do you remove a column from a table?
ALTER TABLE table_name DROP COLUMN column_name;
How do you remove all rows matching a condition?
DELETE FROM table_name WHERE condition;
What is the difference between DELETE and DROP?
DELETE removes rows from a table, while DROP removes a database object such as a table.
What is the difference between SELECT and UPDATE?
SELECT retrieves data, while UPDATE modifies existing data.
What is the difference between SELECT and SELECT DISTINCT?
SELECT can return duplicates, while SELECT DISTINCT removes duplicate results.
What is the difference between SELECT and WHERE?
SELECT specifies what columns to display, while WHERE specifies which rows to include.
Where does a calculation you want to display belong?
In the SELECT portion of the query.
Where does a condition used to filter rows belong?
In the WHERE portion of the query.
How do you display product names and prices below 40.00?
SELECT DISTINCT product_name, unit_price FROM products WHERE unit_price < 40.00;
How do you display products costing more than 40.00?
SELECT product_name, unit_price FROM products WHERE unit_price > 40.00;
How do you find products with zero stock?
SELECT product_id, product_name, stock_quantity FROM products WHERE stock_quantity = 0;
How do you find products with Low Stock status?
SELECT product_name, status FROM products WHERE status = 'Low Stock';
How do you find one specific product by name?
SELECT column_name
FROM table_name
WHERE product_name = 'value';
How do you find products with stock greater than 100?
SELECT product_name, stock_quantity, status FROM products WHERE stock_quantity > 100;
How do you calculate quantity after restocking by 10?
SELECT product_name, stock_quantity, stock_quantity + 10 AS quantityAfterRestock FROM products;
How do you calculate quantity after selling 5 units?
SELECT product_name, stock_quantity, stock_quantity
How do you calculate each product's total stock value?
SELECT product_name, unit_price, stock_quantity, unit_price * stock_quantity AS totalStockValue FROM products;
How do you calculate a 50% price using division?
SELECT product_name, unit_price, unit_price / 2 AS halfPrice FROM products;
How do you calculate leftover units after groups of 10?
SELECT product_name, stock_quantity, stock_quantity % 10 AS remainderAfterBoxesOf10 FROM products;
How do you display products priced at 40.00 or less?
SELECT product_name, unit_price FROM products WHERE unit_price <= 40.00;
How do you calculate a 0.50 discount for in
stock products?
How do you increase Low Stock inventory by 5?
UPDATE products SET stock_quantity = stock_quantity + 5 WHERE status = 'Low Stock';
How do you display total inventory value only for In Stock products?
SELECT product_name, unit_price * stock_quantity AS total_value FROM products WHERE status = 'In Stock';