MySQL Code

0.0(0)
Studied by 6 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/152

encourage image

There's no tags or description

Looks like no tags are added yet.

153 Terms

1
New cards

What SQL statement creates a new database?

CREATE DATABASE database_name;

2
New cards

How do you select a database to work with?

USE database_name;

3
New cards

What SQL statement creates a table?

CREATE TABLE table_name (…);

4
New cards

How do you define a column as an integer primary key?

column_name INT PRIMARY KEY;

5
New cards

How do you define a VARCHAR column with a maximum length?

column_name VARCHAR(length);

6
New cards

How do you define a decimal column with 4 total digits and 2 decimal places?

column_name DECIMAL(4,2);

7
New cards

How do you define an integer column with a default value of 0?

column_name INT DEFAULT 0;

8
New cards

What SQL statement adds a new row to a table?

INSERT INTO table_name VALUES (value1, value2, …);

9
New cards

What is the syntax for inserting values into specific columns?

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

10
New cards

How do you insert multiple rows into a table?

INSERT INTO table_name VALUES (value1, value2, …), (value1, value2, …);

11
New cards

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.

12
New cards

What SQL statement modifies existing data in a table?

UPDATE table_name SET column1 = value1 WHERE condition;

13
New cards

What keyword specifies the values to change in an UPDATE statement?

SET

14
New cards

What keyword limits which rows are modified by UPDATE?

WHERE

15
New cards

What SQL statement removes existing rows from a table?

DELETE FROM table_name WHERE condition;

16
New cards

What SQL statement retrieves data from a table?

SELECT

17
New cards

How do you retrieve every column and every row from a table?

SELECT * FROM table_name;

18
New cards

How do you retrieve specific columns from a table?

SELECT column1, column2, … FROM table_name;

19
New cards

How do you retrieve specific rows from a table?

SELECT * FROM table_name WHERE condition;

20
New cards

How do you retrieve specific columns from specific rows?

SELECT column1, column2, … FROM table_name WHERE condition;

21
New cards

What does SELECT DISTINCT do?

It returns unique values and removes duplicates from the result.

22
New cards

What is the syntax for retrieving unique values from one column?

SELECT DISTINCT column_name FROM table_name;

23
New cards

How do you retrieve unique combinations of two columns?

SELECT DISTINCT column1, column2 FROM table_name;

24
New cards

How do you count distinct values in a column?

SELECT COUNT(DISTINCT column_name) FROM table_name;

25
New cards

How do you count distinct values that meet a condition?

SELECT COUNT(DISTINCT column_name) FROM table_name WHERE condition;

26
New cards

What clause filters rows based on a condition?

WHERE

27
New cards

How do you filter for a numeric value less than 40?

WHERE unit_price < 40.00

28
New cards

How do you filter for a numeric value greater than 40?

WHERE unit_price > 40.00

29
New cards

How do you filter for a value less than or equal to 40?

WHERE unit_price <= 40.00

30
New cards

How do you test whether a value is exactly equal to another value?

WHERE column_name = value

31
New cards

How do you compare a text value in a WHERE clause?

WHERE column_name = 'text'

32
New cards

What comparison operator means not equal?

<> or !=
33
New cards

What does ORDER BY do?

It sorts the rows in the result.

34
New cards

How do you sort by one column?

ORDER BY column_name;

35
New cards

How do you sort by multiple columns?

ORDER BY column1, column2;

36
New cards

How do you sort a column in ascending order?

ORDER BY column_name ASC;

37
New cards

How do you sort a column in descending order?

ORDER BY column_name DESC;

38
New cards

How do you sort unique category and price combinations?

SELECT DISTINCT category, unit_price FROM products ORDER BY category, unit_price;

39
New cards

What does AS do in a SELECT statement?

It gives a column or calculation an alias.

40
New cards

How do you alias a calculated column?

SELECT column1 * column2 AS alias_name FROM table_name;

41
New cards

How do you add two values in a SQL calculation?

column_name + value

42
New cards

How do you subtract one value from another in SQL?

column_name

43
New cards

How do you multiply two column values in SQL?

column1 * column2

44
New cards

How do you divide a column value by a number?

column_name / number

45
New cards

What does the % operator do in SQL?

It returns the remainder after division.

46
New cards

How do you calculate inventory after adding 10 units?

stock_quantity + 10

47
New cards

How do you calculate inventory after selling 5 units?

stock_quantity

48
New cards

How do you calculate total inventory value?

unit_price * stock_quantity

49
New cards

How do you calculate half of a product's price?

unit_price / 2

50
New cards

How do you calculate the remainder after packing stock into boxes of 10?

stock_quantity % 10

51
New cards

How do you calculate a promotional price that is 0.50 lower?

unit_price

52
New cards

How do you display a calculation with a custom column name?

SELECT calculation AS alias_name FROM table_name;

53
New cards

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;

54
New cards

How do you update only rows with a specific text status?

UPDATE table_name SET column_name = value WHERE status = 'status_value';

55
New cards

How do you add a new column to an existing table?

ALTER TABLE table_name ADD COLUMN column_name data_type;

56
New cards

How do you modify an existing column's data type?

ALTER TABLE table_name MODIFY COLUMN column_name data_type;

57
New cards

How do you remove a column from a table?

ALTER TABLE table_name DROP COLUMN column_name;

58
New cards

How do you remove all rows matching a condition?

DELETE FROM table_name WHERE condition;

59
New cards

What is the difference between DELETE and DROP?

DELETE removes rows from a table, while DROP removes a database object such as a table.

60
New cards

What is the difference between SELECT and UPDATE?

SELECT retrieves data, while UPDATE modifies existing data.

61
New cards

What is the difference between SELECT and SELECT DISTINCT?

SELECT can return duplicates, while SELECT DISTINCT removes duplicate results.

62
New cards

What is the difference between SELECT and WHERE?

SELECT specifies what columns to display, while WHERE specifies which rows to include.

63
New cards

Where does a calculation you want to display belong?

In the SELECT portion of the query.

64
New cards

Where does a condition used to filter rows belong?

In the WHERE portion of the query.

65
New cards

How do you display product names and prices below 40.00?

SELECT DISTINCT product_name, unit_price FROM products WHERE unit_price < 40.00;

66
New cards

How do you display products costing more than 40.00?

SELECT product_name, unit_price FROM products WHERE unit_price > 40.00;

67
New cards

How do you find products with zero stock?

SELECT product_id, product_name, stock_quantity FROM products WHERE stock_quantity = 0;

68
New cards

How do you find products with Low Stock status?

SELECT product_name, status FROM products WHERE status = 'Low Stock';

69
New cards

How do you find one specific product by name?

SELECT column_name

FROM table_name

WHERE product_name = 'value';

70
New cards

How do you find products with stock greater than 100?

SELECT product_name, stock_quantity, status FROM products WHERE stock_quantity > 100;

71
New cards

How do you calculate quantity after restocking by 10?

SELECT product_name, stock_quantity, stock_quantity + 10 AS quantityAfterRestock FROM products;

72
New cards

How do you calculate quantity after selling 5 units?

SELECT product_name, stock_quantity, stock_quantity

73
New cards

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;

74
New cards

How do you calculate a 50% price using division?

SELECT product_name, unit_price, unit_price / 2 AS halfPrice FROM products;

75
New cards

How do you calculate leftover units after groups of 10?

SELECT product_name, stock_quantity, stock_quantity % 10 AS remainderAfterBoxesOf10 FROM products;

76
New cards

How do you display products priced at 40.00 or less?

SELECT product_name, unit_price FROM products WHERE unit_price <= 40.00;

77
New cards

How do you calculate a 0.50 discount for in

stock products?

78
New cards

How do you increase Low Stock inventory by 5?

UPDATE products SET stock_quantity = stock_quantity + 5 WHERE status = 'Low Stock';

79
New cards

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';

80
New cards
What is the basic syntax pattern for creating a database?
CREATE DATABASE database_name;
81
New cards
What is the basic syntax pattern for selecting a database?
USE database_name;
82
New cards
What is the basic syntax pattern for creating a table?
CREATE TABLE table_name (column_name data_type, ...);
83
New cards
What is the basic syntax pattern for inserting a complete row?
INSERT INTO table_name VALUES (value1, value2, ...);
84
New cards
What is the basic syntax pattern for inserting values into specific columns?
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
85
New cards
What is the basic syntax pattern for inserting multiple rows?
INSERT INTO table_name VALUES (...), (...), (...);
86
New cards
What is the basic syntax pattern for updating existing data?
UPDATE table_name SET column1 = value1 WHERE condition;
87
New cards
What is the basic syntax pattern for deleting rows?
DELETE FROM table_name WHERE condition;
88
New cards
What is the basic syntax pattern for selecting all columns?
SELECT * FROM table_name;
89
New cards
What is the basic syntax pattern for selecting specific columns?
SELECT column1, column2 FROM table_name;
90
New cards
What is the basic syntax pattern for filtering selected rows?
SELECT columns FROM table_name WHERE condition;
91
New cards
What is the basic syntax pattern for selecting unique values?
SELECT DISTINCT column_name FROM table_name;
92
New cards
What is the basic syntax pattern for selecting unique combinations of columns?
SELECT DISTINCT column1, column2 FROM table_name;
93
New cards
What is the basic syntax pattern for counting distinct values?
SELECT COUNT(DISTINCT column_name) FROM table_name;
94
New cards
What is the basic syntax pattern for sorting query results?
SELECT columns FROM table_name ORDER BY column_name;
95
New cards
What is the basic syntax pattern for sorting by multiple columns?
SELECT columns FROM table_name ORDER BY column1, column2;
96
New cards
What is the basic syntax pattern for adding a column?
ALTER TABLE table_name ADD COLUMN column_name data_type;
97
New cards
What is the basic syntax pattern for modifying a column's data type?
ALTER TABLE table_name MODIFY COLUMN column_name data_type;
98
New cards
What is the basic syntax pattern for removing a column?
ALTER TABLE table_name DROP COLUMN column_name;
99
New cards
What is the basic syntax pattern for using a calculation in SELECT?
SELECT column1, calculation AS alias_name FROM table_name;
100
New cards
What is the basic syntax pattern for filtering text values?
WHERE column_name = 'text_value';