SQL syntax Flashcards
What will the following query do:
SELECT name, hour_salary*176 AS salary
FROM employee
WHERE id = 3
It will give you the name and hourly salary multiplied by 176 of the employee with employee id = 3 from the table called employee.
AS indicates that we are renaming the column hourly salary to salary in the result.
SELECT DISTINCT department_id
FROM employee
WHERE salary > 230
Explain the SELECT DISTINCT clause.
It will give you department_id from table employee but only the rows where the value in the salary attribute is higher than 230 and gives only distinct (unique department_ids).
SELECT DISTINCT means that the result will be only unique values of the queried attribute.
Explain the ORDER BY clause
If you add an order by clause to your query that attribute can be ordered by ascending or descending values. Ascending is default.
SELECT number
FROM numbers
WHERE number > 5
ORDER BY number DESC
How can you query for all last names that starts/ends with with the letter K?
SELECT lastname
FROM employee
WHERE last name LIKE K%
You want the full name from the employee table with last name first, write the query
SELECT first name, last name
FROM employee
ORDER BY last name, first name
What is ascending and descending order when using the ORDER BY clause?
Ascending: Lowest to highest value or alphabetical
Descending: Highest to lowest or reverse alphabetical
How can we see all information from two tables in one query?
For example:
SELECT *
FROM employee, department
Write a query that shows first name and last name from an employee table and the name of the department that employee works at which is stored in a department table.
DepartmentID is foreign key in employee table.
SELECT employee.firstname, employee.lastname, department.department_name
FROM employee, department
WHERE employee.departmentID = department.departmentID
This is called a joined query because the query involves two tables.
Explain the count(*) command
If you use
SELECT count(*) for some attribute/table you will get the number of rows. Considers NULL values as well.
You can fit it with a WHERE clause to be more specific.
For example:
SELECT count(*)
FROM department, product
WHERE product.product_id = department.department_id;
When doing a joined condition do you always need to reference the table name before the attribute you want?
If the attribute name is unique in the whole relational model then you can drop the table reference.
What are the different ways of referencing tables in a joined query?
Can you use both in all situations?
Write the whole table name:
SELECT employee.ssn
FROM employee
Or use an alias for the table name
SELECT e.ssn
FROM employee AS e
If a query joins a table that has the same name then aliases is the only way to reference them.
Write a query that selects the first name and last name of a user from the “user” table that has made an order.
In an “order” table userID is FK referencing the user table.
Use aliases.
SELECT u.first_name, u.last_name
FROM user AS u, order_ AS o
WHERE u.userID = o.userID;
Write a query that select the attributes from the table “employee” and the name of the department they work at. Department title is stored in “department” and PK is dep_ID which is FK in the employee table.
Write it in two different ways
SELECT employee.*, department.title
FROM employee, department
WHERE employee.dep_ID = department.dep_ID
or
SELECT employee.*, department.title
FROM employee
JOIN department ON employee.dep_ID = department.dep_ID.
Write a query that selects first names that starts with R from the table “employee” and the name of the department they work at. Department title is stored in “department” and PK is dep_ID which is FK in the employee table.
SELECT employee.firstname, department.title
FROM employee, department
WHERE employee.dep_ID = department.dep_ID AND employee.firstname LIKE ‘R%’
What is the syntax for creating a table?
CREATE TABLE table_name (
attribute1 type of value key/constraints,
attribute2 …
)
What is the syntax for creating a table employee and making the ssn PK and email unique?
CREATE TABLE employee
(
ssn int NOT NULL PRIMARY KEY,
email VARCHAR (50) UNIQUE
)
What is the syntax for creating a table employee that has both ssn and ID as PK and salary as decimal?
CREATE TABLE employee
(
ssn int NOT NULL,
ID int NOT NULL,
salary decimal (10, 2)
PRIMARY KEY(ssn, ID)
)
What is the syntax for creating a table employee with PK ssn, salary and dep_ID as FK referencing the table department.
CREATE TABLE employee
(
ssn int NOT NULL,
salary int,
dep_ID int,
PRIMARY KEY(ssn),
FOREIGN KEY(dep_ID) REFERENCING department(dep_ID)
)
How can we specify what we want to happen to the rows in a table that has an attribute as FK if we update or delete a row in the table that is the reference for the FK?
Reference integrity constraint says that we cannot delete a row that is used as reference in another table.
We can solve this with reference options CASCADE, SET NULL or NO ACTION.
When creating the original table we specify which reference option to use.
CREATE TABLE name
(
attribute1 int PRIMARY KEY,
FOREIGN KEY(attribute) REFERENCES table name(attribute name)
[ON DELETE SET NULL]
[ON UPDATE CASCADE]
)
Explain the different reference options
CASCADE = Deletes/updates matching rows in referencing tables as well.
NO ACTION = deletes/updates will fail if there are matching rows
SET NULL = FK for matching rows in referencing tables will be set to null. Null must be allowed when creating the referencing table.
What is the syntax for inserting values into a table?
INSERT INTO table name(attribute1, attribute2…)
VALUES (1, abc…)
What is the UNION operation?
UNION combines the result of 2 or more SELECT queries.
SELECT attribute
FROM table
WHERE condition
UNION
SELECT attribute
FROM table
WHERE condition
Note that without UNION ALL you will only get distinct results.
What is the INTERSECT operation?
The intersect will retrieve distinct common records from the result of 2 or more SELECT queries.
SELECT first_name
FROM employee
INTERSECT
SELECT first_name
FROM customer
This will give the distinct first_names that exist in both employee and customer.
The selected attributes must match in both SELECT queries.
What is the EXCEPT operation?
The query returns distinct rows from the first result set which are not in the second result set.
SELECT first_name
FROM employee
EXCEPT
SELECT first_name
FROM customer
This will give the distinct first names that exists in employee but not in customer.
