Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

period 2 > SQL syntax > Flashcards

SQL syntax Flashcards

1
Q

What will the following query do:

SELECT name, hour_salary*176 AS salary
FROM employee
WHERE id = 3

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

SELECT DISTINCT department_id
FROM employee
WHERE salary > 230

Explain the SELECT DISTINCT clause.

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Explain the ORDER BY clause

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How can you query for all last names that starts/ends with with the letter K?

A

SELECT lastname
FROM employee
WHERE last name LIKE K%

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

You want the full name from the employee table with last name first, write the query

A

SELECT first name, last name
FROM employee
ORDER BY last name, first name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is ascending and descending order when using the ORDER BY clause?

A

Ascending: Lowest to highest value or alphabetical

Descending: Highest to lowest or reverse alphabetical

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

How can we see all information from two tables in one query?

A

For example:

SELECT *
FROM employee, department

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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.

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Explain the count(*) command

A

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;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

When doing a joined condition do you always need to reference the table name before the attribute you want?

A

If the attribute name is unique in the whole relational model then you can drop the table reference.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are the different ways of referencing tables in a joined query?

Can you use both in all situations?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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.

A

SELECT u.first_name, u.last_name
FROM user AS u, order_ AS o
WHERE u.userID = o.userID;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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.

A

SELECT employee.firstname, department.title
FROM employee, department
WHERE employee.dep_ID = department.dep_ID AND employee.firstname LIKE ‘R%’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the syntax for creating a table?

A

CREATE TABLE table_name (
attribute1 type of value key/constraints,
attribute2 …
)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is the syntax for creating a table employee and making the ssn PK and email unique?

A

CREATE TABLE employee
(
ssn int NOT NULL PRIMARY KEY,

email VARCHAR (50) UNIQUE
)

17
Q

What is the syntax for creating a table employee that has both ssn and ID as PK and salary as decimal?

A

CREATE TABLE employee
(
ssn int NOT NULL,
ID int NOT NULL,
salary decimal (10, 2)
PRIMARY KEY(ssn, ID)
)

18
Q

What is the syntax for creating a table employee with PK ssn, salary and dep_ID as FK referencing the table department.

A

CREATE TABLE employee
(
ssn int NOT NULL,
salary int,
dep_ID int,
PRIMARY KEY(ssn),
FOREIGN KEY(dep_ID) REFERENCING department(dep_ID)
)

19
Q

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?

A

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]
)

20
Q

Explain the different reference options

A

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.

21
Q

What is the syntax for inserting values into a table?

A

INSERT INTO table name(attribute1, attribute2…)
VALUES (1, abc…)

22
Q

What is the UNION operation?

A

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.

23
Q

What is the INTERSECT operation?

A

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.

24
Q

What is the EXCEPT operation?

A

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.

25
Q

What is the EXCEPT ALL operation?

A

It will return duplicate rows that exist in the first result set but not in the second result set.

If something exists in the first result set 3 times and once in the second result set it will be twice in the result (the difference) if the number in the first result > the number in the second result. If not then it will give nothing at all.

26
Q

Does aggregate functions consider null values?

A

Only count().

27
Q

What will the following query give?

A

SELECT count(*), avg(salary)
FROM employee

The number of employees and their average salary.

28
Q

How can we count how many employees work at department 1?

A

SELECT count(*)
FROM employee
WHERE dep_ID = 1

29
Q

What is the GROUP BY clause?

A

GROUP BY clause is used to group rows that have the same values in specified columns into summary rows.

The basic syntax is

SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1

30
Q

We want to know the average salary per department but only include the ones with salary over 10 000.

A

SELECT dep_ID, avg(salary)
FROM employee
WHERE salary > 10 000
GROUP BY dep_ID

31
Q

What is the HAVING clause?

A

The having clause is used with the GROUP BY clause to choose the groups that will be included in the result.

For example:

SELECT dep_ID, avg(salary)
FROM employee
GROUP BY dep_ID
HAVING count(*) > 5

This will only give the departments that have at least 5 employees.

32
Q

We want to know the minimum salary for employees working at departments with ID > 3.

A

SELECT dep_ID, min(salary) AS min_salary
FROM employee
GROUP BY dep_ID
HAVING dep_ID > 3.

33
Q

Name the aggregate functions

A

Avg()
Sum()
Min()
Max()
Count()

34
Q

Use a nested query to find the employees with the lowest salary.

A

SELECT *
FROM employee
WHERE salary = (
SELECT min(salary) FROM employee
)

The inner query finds the minimum salary in employee and the outer query selects all rows with that salary.

35
Q

What will be the result if you use the count(*) when you cross two tables?

A

You will get the number of rows times each other.

36
Q

SELECT Stud_Name, Adv_Name FROM
STUDENTS LEFT OUTER JOIN ADVISORS ON Advisor_ID=Adv_ID

What will this query do?

A

Returns all records from the left table (STUDENTS), and the matched records from the right table (ADVISORS)

It is going to choose all student names and the matching advisors looking at the advisor id. If the values does not match then it will give null in the advisor name.

37
Q

SELECT Stud_Name, Adv_Name
FROM STUDENTS
INNER JOIN ADVISORS ON Advisor_ID = Adv_ID;

What will this query do?

A

The inner join keyword means that you only get the rows where the values are matching in both tables.

You will get the student names and their advisors but only where the adv_id matches.

LEFT outer join will instead give all the rows for the table given to the left of the left outer joint query and put null in the other where there is no match.

38
Q

What does the SUM() aggregate do?

A

It sums the numerical values of a column. You can specify which rows you want to sum in the WHERE statement.

You can multiply the sum

Ex.
SELECT sum(T1.C2 * T2.C2)
WHERE T1.C1 = T2.C1
GROUP BY T1.C1

This query will sum the values in both C2 columns separately where the values in C1 are the same. It will then multiply them by each other.

It will do this in groups decided by T1.C1.

39
Q

How does the count operator work in joined queries?

A

It first finds all possible pairs of rows and then filters based on the WHERE clause.

period 2 (6 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions