SQL

Question 1

base command to make a new table

Answer 1

CREATE schema_name.table_name

Question 2

command to make a new database or schema

Answer 2

CREATE database_name

Question 3

command to delete a database or schema

Answer 3

DROP schema_name

Question 4

add attributes syntax when creating a table

Answer 4

CREATE tablename
(attributename, datatype, DEFAULT defaultvalue);

Question 5

constrain table column not to have null values

Answer 5

NOT NULL

Question 6

constrain table column value to meet a condition

Answer 6

CHECK (condition)

Question 7

constrain a table column to refuse duplicate values

Answer 7

UNIQUE or UNIQUE(attribute list)

Question 8

set table attribute as the primary key

Answer 8

PRIMARY KEY

Question 9

set multiple table attributes as the primary key

Answer 9

PRIMARY KEY (attribute list)

Question 10

set foreign key for a table attribute

Answer 10

… REFERENCES schema.table(attribute)

Question 11

modify existing values on a table

update the salary of employees in the ‘employees’ table with a department ID of 5 to 50000

Answer 11

UPDATE employees
SET salary = 50000
WHERE department_id = 5;

Question 12

modify the structure of an existing table, such as adding, deleting, or

add a ‘birthdate’ column of type DATE to the ‘employees’ table

Answer 12

ALTER TABLE employees
ADD birthdate DATE;

Question 13

modify the structure of an existing table, such as adding, deleting, or

remove the ‘birthdate’ column from the ‘employees’ table

Answer 13

ALTER TABLE employees
DROP COLUMN birthdate;

Question 14

delete records from a table

delete all records from the ‘employees’ table where the department ID is 5

Answer 14

DELETE FROM employees
WHERE department_id = 5;

Question 15

retrieve all columns from the ‘employees’ table

Answer 15

SELECT * FROM employees;

Question 16

select specific columns from a table

select the ‘name’ and ‘email’ columns from the ‘employees’ table

Answer 16

SELECT name, email
FROM employees;

Question 17

filter rows

select all ‘employees’ with a ‘salary’ greater than 50000

Answer 17

SELECT *
FROM employees
WHERE salary > 50000;

Question 18

sort all ‘employees’ by their salary in descending order

Answer 18

SELECT *
FROM employees
ORDER BY salary DESC;

Question 19

get the top 5 highest-paid (‘salary’) ‘employees’

Answer 19

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

Question 20

count the number of ‘employees’ in each department

‘department_id’

Answer 20

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id;

Question 21

find departments with more than 10 ‘employees’

‘department_id’

Answer 21

SELECT
department_id,
COUNT()
FROM employees
GROUP BY department_id
HAVING COUNT() > 10;

Question 22

how can I query a column that contains a space or other special character in the name?

Answer 22

put quotes around it (note that this will make it case sensitive)

Question 23

write a single line comment

Answer 23

use -- (double dash)

-- SELECT statement that will not run

Question 24

write a multi-line comment

Answer 24

/* put comment inside slash stars */