SQL Flashcards
base command to make a new table
CREATE schema_name.table_name
command to make a new database or schema
CREATE database_name
command to delete a database or schema
DROP schema_name
add attributes syntax when creating a table
CREATE tablename
(attributename, datatype, DEFAULT defaultvalue);
constrain table column not to have null values
NOT NULL
constrain table column value to meet a condition
CHECK (condition)
constrain a table column to refuse duplicate values
UNIQUE or UNIQUE(attribute list)
set table attribute as the primary key
PRIMARY KEY
set multiple table attributes as the primary key
PRIMARY KEY (attribute list)
set foreign key for a table attribute
… REFERENCES schema.table(attribute)
modify existing values on a table
update the salary of employees in the ‘employees’ table with a department ID of 5 to 50000
UPDATE employees
SET salary = 50000
WHERE department_id = 5;
modify the structure of an existing table, such as adding, deleting, or
add a ‘birthdate’ column of type DATE to the ‘employees’ table
ALTER TABLE employees
ADD birthdate DATE;
modify the structure of an existing table, such as adding, deleting, or
remove the ‘birthdate’ column from the ‘employees’ table
ALTER TABLE employees
DROP COLUMN birthdate;
delete records from a table
delete all records from the ‘employees’ table where the department ID is 5
DELETE FROM employees
WHERE department_id = 5;
retrieve all columns from the ‘employees’ table
SELECT * FROM employees;
select specific columns from a table
select the ‘name’ and ‘email’ columns from the ‘employees’ table
SELECT name, email
FROM employees;
filter rows
select all ‘employees’ with a ‘salary’ greater than 50000
SELECT *
FROM employees
WHERE salary > 50000;
sort all ‘employees’ by their salary in descending order
SELECT *
FROM employees
ORDER BY salary DESC;
get the top 5 highest-paid (‘salary’) ‘employees’
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;
count the number of ‘employees’ in each department
‘department_id’
SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id;
find departments with more than 10 ‘employees’
‘department_id’
SELECT
department_id,
COUNT()
FROM employees
GROUP BY department_id
HAVING COUNT() > 10;
how can I query a column that contains a space or other special character in the name?
put quotes around it (note that this will make it case sensitive)
write a single line comment
use -- (double dash)
-- SELECT statement that will not run
write a multi-line comment
/* put comment inside slash stars */
