Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

23 cards

()

Section 1

Preview this deck

All

Front

1 / 23

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 14, 2020

Cards (23)

Section 1

(23 cards)

All

Front

All values returned by the subquery must satisfy the condition Ex) Return the employees who make more than the employees with title 'ME' make. SELECT ename FROM emp as E WHERE salary > ALL (SELECT salary FROM emp WHERE title = 'ME')

Back

How to Join Queries

Front

Ex) return the employees who are assigned to the 'Management' department SELECT ename FROM emp, dept WHERE dname = 'Management' and emp.do = dept.dno Ex) Return the department names and the projects in each department: SELECT dname, pname FROM dept, proj WHERE dept.dno = proj.dno

Back

Having

Front

Applied after the group by clause and aggregate functions are calculated. It is used to filter out groups that do not match certain criteria Ex) Return the title and number of employees of that title where the number of employees of the title is at least 2 SELECT title, COUNT(eno) AS numpEmp FROM emp GROUP BY title HAVING COUNT(eno) >= 2

Back

Row subquery

Front

Returns a single row which may have multiple columns

Back

Aggregate Functions

Front

The five basic aggregate functions are: Count: returns the # of values in a column Sum: Returns the sum of the values in a column Avg: Returns the average of the values in a column Min: Returns the smallest value in a column Max: Returns the largest value in a column

Back

Limit

Front

If you only want the first N rows. SELECT ename, salary FROM emp ORDER BY salary DESC LIMIT 5

Back

Aggregate Function: Return the number of employees and their average salary:

Front

SELECT COUNT(eno) AS numEmp, AVG(salary) AS avgSalary FROM emp

Back

Return the employee number, project number, and number of hours worked where the hours worked is > 50

Front

SELECT eno, pno, hours FROM workson WHERE hours > 50

Back

Group By

Front

Groups the tuples based on the values of the attributes specified Ex) for each employee title, return the number of employees with that title and the minimum, maximum and average salary SELECT title, COUNT(eno) AS numEmp, MIN(salary) as MinSal MAX(salary) as MaxSal AVG(salary) as AvgSal FROM EMP GROUP BY Title

Back

Return all information on employees:

Front

SELECT * FROM emp

Back

Exists

Front

Checks whether the result of a nested query is empty or not Ex) Return all employees who have the same name as someone else in the company SELECT ename FROM emp as E WHERE EXISTS (SELECT FROM emp as E2 WHERE E.ename = E2.ename AND E.eno <> E2.eno)

Back

Ordering

Front

The query result returned is not ordered on any attribute by default. We can order the data using the ORDER BY clause: SELECT ename, salary, bdate FROM emp WHERE salary > 300000 ORDER BY salary DESC, ename ASC

Back

Select Into

Front

The result of a select statement can be stored in a temporary table using the INTO keyword SELECT E.ename INTO davisMgr FROM emp as E, emp as M WHERE E.supereno = M.eno and M.ename = 'R. Davis'

Back

Remove duplicates:

Front

SELECT DISCTINCT title FROM emp

Back

Like

Front

For string values attributes, Like is used to search for partial matches Ex) Return all employee names that start with 'A' SELECT ename FROM emp WHERE ename LIKE 'A%'

Back

Return the birth date and salary of employee 'J. Doe':

Front

SELECT bdate, salary FROM emp WHERE ename = 'J. Doe'

Back

Any

Front

Any value returned by the subquery can satisfy the condition

Back

Null

Front

To indicate that a given attribute does not have a value. Ex) return all employees who are not in a department SELECT ename FROM emp WHERE dno IS NULL

Back

Between

Front

Requesting where one attribute value must be in a range of values. Ex) Return the employees who make at least 20k and less than or equal to 45k SELECT ename FROM emp WHERE salary BETWEEN 20000 and 450000

Back

Table Subquery

Front

Returns one or more columns and multiple rows Ex) Return all departments who have a project with a budget greater than 300k SELECT dname FROM dept WHERE dno IN (SELECT dno FROM proj WHERE budget > 300000)

Back

In

Front

to specify that an attribute value should be in a given set of values ex) Return all employees who are in any one of the departments {D1, D2, D3} SELECT ename FROM emp WHERE dno IN ('D1','D2','D3')

Back

How to rename

Front

Renaming is accomplished using the keyword AS: SELECT ename, pname, salary AS pay FROM emp, workson, proj WHERE emp.eno = workson.eno and ename = 'A. Lee' and proj.pno = workson.pno

Back

Scalar subqueries

Front

Return a single value. Often value is then used in a comparison Ex) Return the employees that are in the 'Accounting' Department SELECT ename FROM emp WHERE dno = (SELECT dno FROM dept WHERE dname = 'Accounting')

Back