SQL

How do you comment in PostreSQL?

2 dashes --

NVL(comm, 0)

A function that can be used to substitute another value for a NULL value during calculations. Can only convert null to number or null to string by using to_char()

What is a good rule of thumb when using GROUP BY clause?

A good rule of thumb when using GROUP BY is to include all the non-aggregate function columns in the SELECT statement in the GROUP BY clause.

What is a correlated subquery - def. 2?

A nested query but the inner query refers to the same table as the outer table.

What is a correlated subquery?

A query inside another query, where the inner query refers to something in the outer query.

Code to build statistics for an already created index

ALTER INDEX emp_name_idx REBUILD COMPUTE STATISTICS

Command to add a column in a table

ALTER TABLE employees ADD store_id number not null; -- gives error because there is data in the table while the field can't be null ALTER TABLE employees ADD store_id number; -- works fine now

What is the default sort order is we use order by?

ASC is the default sort order.

Should all tables have a unique identifier?

All well-designed tables should have a unique identifier

Code to drop an index

DROP INDEX emp_job_idx

What is a table?

It is an object in a database that stores data

What does NULL mean?

It means that the cell is completely empty. There is nothing there. NULL is different from 0.

Grouping Function

Many inputs, one output. Those are grouping functions. min, max, avg, count,

Order by 1

Means order by the first column

How do you display all the null values for email?

Null values are displayed using the following syntax: SELECT * FROM employees WHERE email is NULL

When is ORDER BY processed?

ORDER BY is one of the last clauses processed by the database query engine.

What are some features of a primary key?

Primary keys can't repeat, can't be null, usually the first column of the table, can also be a combination of multiples columns.

Write a query which shows the names of those employees that are not manager nor salesman and have a salary greater than or equal to 2000.

SELECT * FROM EMP WHERE JOB != 'MANAGER' AND JOB != 'SALESMAN' AND SAL >= 2000

Write a SQL statement that retrieves all rows from 'employees' table for the sports department

SELECT * FROM employees WHERE department = 'Sports'; Note: Sports is case-sensitive

Write a SQL statement that retrieves all rows from 'employees' table

SELECT * FROM employees;

Write a query that retrieves suppliers that work in either Georgia or California

SELECT * FROM suppliers WHERE state = 'Georgia' OR state = 'California';

Write a query that retrieves suppliers with the characters "wo" and the character "I" or "i" in their name

SELECT * FROM suppliers WHERE supplier_name like '%wo%' AND (supplier_name like '%i%' OR supplier_name like '%I%');

Write a query that retrieves suppliers on which a minimum of 37,000 and a maximum of 80,000 was spent.

SELECT * FROM suppliers WHERE total_spent >= 37000 AND total_spent <= 80000; You may also use the BETWEEN operator to solve this problem.

A complex where clause

SELECT * FROM EMP WHERE (SAL BETWEEN 1100 AND 5000) AND (SAL !=3000) AND (COMM IS NULL OR COMM = 0)

A query with LIKE

SELECT * FROM EMP WHERE JOB LIKE 'S%'

What is a more succinct way to write this query: SELECT ENAME, HIREDATE FROM EMP WHERE ENAME = 'FORD' OR ENAME = 'SMITH'

SELECT ENAME, HIREDATE FROM EMP WHERE ENAME IN ('FORD', 'SMITH')

What is the oppoiste of: SELECT ENAME, HIREDATEFROM EMPWHERE ENAME IN ('FORD', 'SMITH')

SELECT ENAME, HIREDATEFROM EMPWHERE ENAME NOT IN ('FORD', 'SMITH')

What is an alternative to a subquery in the select clause? SELECT first_name, department, (select count(*) from employees e2 where e2.department = e1.department) FROM employees e1

SELECT first_name, department, COUNT(*) OVER(PARTITION BY department) FROM employees We can use the EXCEPT command to subtract the 2 statements to prove that the 2 queries are equivalent. This statement is better in terms of database performance and developer ease in terms of code writing.

Write a query that will post the first value of a data set to the right-hand column. Or, write a query that will post the highest salary of an employee's department next the employee's salary.

SELECT first_name, department, salary, FIRST_VALUE(salary) OVER(PARTITION BY department ORDER BY salary DESC) as first_value FROM employees MAX will also work here. There is also something called NTH_VALUE to post the nth value of the list. SELECT first_name, department, salary, NTH_VALUE(salary, 5) OVER(PARTITION BY department ORDER BY salary DESC) as nth_value FROM employees

Write a query to show the previous salary next to the employee's salary.

SELECT first_name, department, salary, LAG(salary) OVER() as previous_value FROM employees. The last row won't have any salary because there is no value after the last row.

Write a query to show the closest higher salary next to the employee's salary.

SELECT first_name, department, salary, LAG(salary) OVER(ORDER BY salary DESC) as closest_higher_salary FROM employees.

Write a query to show the lead salary next to the employee's salary.

SELECT first_name, department, salary, LEAD(salary) OVER() as next_value FROM employees. The last row won't have any salary because there is no value after the last row.

Write a query to show the closest lower salary next to the employee's salary.

SELECT first_name, department, salary, LEAD(salary) OVER(ORDER BY salary DESC) as closest_lowest_salary FROM employees.

Write a query to rank the salaries of individual employees within each division

SELECT first_name, department, salary, NTILE(5) OVER(PARTITION BY department ORDER BY salary DESC) FROM employees

Write a query to rank the salaries of all the employees in each department

SELECT first_name, department, salary, RANK() OVER(PARTITION BY department ORDER BY salary DESC) FROM employees (you can also use WHERE RANK = 2, to see all the second highest salaries in each department)

Write a boolean expression in SQL

SELECT first_name, last_name, (salary > 120000) FROM employees

In which clauses can subqueries be used?

Subqueries can be used in the FROM, WHERE, SELECT and even the HAVING clause

Describe the Group By clause?

The GROUP BY clause is used to tell SQL what level of granularity the aggregate function should be calculated in. The level of granularity is represented by the columns in the SELECT statement that are not aggregate functions.

ALTER TABLE table MODIFY name varchar2(50) not null;

The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD or MODIFY), it adds a column or changes column characteristics.

Which takes precedence? There WHERE clause or the windowing function?

The WHERE clause takes precedence over the windowing function. The windowing function runs towards the end of the query.

What does parenthesis do in a SQL statement?

The presence of parenthesis causes the conditions within them to be evaluated together

Single Row Function

These type of functions are the one who work on single row and return one output per row.

VARCHAR2

This is used more often in the industry than VARCHAR

TO_CHAR function

To convert a date or number to a string. Really useful date functions at Video 18 and 19 of the Oracle course.

Code to update a table

UPDATE your_table SET column = value WHERE some criteria UPDATE EMPLOYEES SET STORE_ID = 3 WHERE ENAME IN ('KING', 'BLAKE', 'CLARK');

How do you display all the values that are not NULL for email?

Values that not null are displayed using the following syntax: SELECT * FROM employees WHERE email is NOT NULL

VARCHAR

Variable-length character data

Syntax for not equal in SQL

WHERE NOT, DEPARTMENT != 'Sports' DEPARTMENT <> 'Sports' <> means show everything less than and greater than this value; in other words, not equal to Sports

Indexes

We build indexes on important columns in the database. It is not a good idea to build it for every single column in the database because it takes up space. They are an important part of performance optimization. Without an index, the system has to scan every single column in the table.

Can we use where count(*) =2?

We cannot use any type of grouping function in the where clause. We have to use the HAVING clause at that time. select job, count(*) from emp group by job having count(*) = 2

How do you display multiple departments using the IN statement?

We display multiple departments by using the OR clause multiple times or by using the IN clause: SELECT * FROM employees WHERE department IN ('Sports', 'First Aid', 'Toys', 'Garden')

How do you display multiple departments using the OR statement?

We display multiple departments by using the OR clause multiple times: SELECT * FROM employees WHERE department = 'Sports', OR department = 'First Aid', OR department = 'Toys', OR department = 'Garden')

When should we use "=" and when should you use "IN"?

We should use "=" when we are checking for a single value. We should use "IN" when we are checking for multiple values.

Give an example of how to use the BETWEEN clause.

We use it after the WHERE clause. SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 1000 AND 2000. 1000 and 2000 also qualifies in the between statement

Can you put a different column / variable in 'group by' compared to the select clause?

Yes, for example: select salary group by region or, select count(*) group by gender or, select min(id) from employee group by name (this query is from Imtiaz Ahmad's SQL course)

Is it possible for a table to have more than one foreign key?

Yes, it is possible for a table to have 2 or more foreign keys.

Where can you use subquries?

You can use subqueries in the SELECT clause, FROM clause and the WHERE clause, and many other places

COMMIT;

You have to run commit for the insert into ... values to show up in the tables

TRUE or FALSE Question: Giving the date arguments in chronological order to the MONTHS_BETWEEN function will result in an error.

You may give the 2 dates in any order you please. If the dates are in chronological order, the result will be a negative number. If the dates are in reverse chronological order, the answer will be a positive number.

varchar

allows numbers and words

COUNT(column_name) over null values?

count doesn't pick up the null values. So it will undercount the number of rows in your table. Use count(*) instead.

What type of data can the between operator be used for?

dates, numbers, and textual data

LAST_DAY(date)

last_day is a date function that requires a date as an argument. It returns the last day of the month in which the given date falls. The argument is required for this function to work properly.

What are some numeric functions in SQL?

round (107.088, 2 ) result 107.09, trunc(108.088, 2) result 107.08,

Write a query that shows, "Hello, my name is <emp_name>

select 'Hello, my name is ' || ename from emp

Write a query that returns those employees who have commissions greater than their salaries.

select * from emp where comm > sal

Do dates have to wrapped in quotes?

select * from emp where hiredate between '05/01/1981' and '12/09/1982'

Write a query which shows both Clerk and Salesman for the Job column.

select * from emp where job = 'CLERK' or job = 'SALESMAN'

A more comprehensive way to write a manager query

select * from emp where lower(job) like %manager%

Write a query that returns those employees who are not managers and who have salaries greater than 2500 and also work in department number 20.

select * from emp where job != 'MANAGER' AND sal > 2500 AND DEPTNO = 20;

Write a concat within a concat

select concat(concat(lower(ename), ' IS THE NAME and their job is: '), upper(job)) from emp

Write a query using CUBE and explain the query.

select continent, country, city, sum(units_sold) from sales group by cube(continent, country, city) group by cubes gives more permutation of data

Write a query using GROUPING SETS and explain the query. Use the 3 Cs for your example.

select continent, country, city, sum(units_sold) from sales group by grouping sets(continent, country, city)

Write a query using ROLLUP and explain the query.

select continent, country, city, sum(units_sold) from sales group by rollup(continent, country, city) Note: group by grouping sets and rollups give exactly the same results

select job from emp group by job Is equivalent to?

select distinct(job) from emp

Write a query to change technical column names to more business friendly names

select empno as "Employee Number", ename as "Name", sal as "Salary" from emp

Write a query which uses pipes twice

select ename || ' makes $' || sal || ' per month' as "employee income" from emp

What are some date functions in SQL?

sysdate, systimestamp, add_months, months_between, trunc(date, 'MONTH' (or 'YEAR')), to_char(), to_date('str', 'fmt') ex: select sysdate from dual, select months_between('12/4/2013', ' 12/4/2012') from dual

What are some string functions in SQL?

upper, lower, concat, initcap, length, substr, Lpad, rpad, ltrim, rtrim, etc.

Memorize the 5 steps in a SQL query

1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY

Functions and Arguments

Functions call a code which does something. And Arguments are what goes inside the functions. They are like input variables for the function.

DESCRIBE tablename

Gives us information about the table.

What are the rules for non-aggregated columns in the select statement when you are using the group by clause?

If you add any non-aggregated column in the select statement and you are also using group by, then you have to also include it in the group by clause. Examples of non-aggregated columns are name and gender.

Code to build an index

CREATE INDEX emp_name_idx ON employees (ename) CREATE INDEX emp_name_job_date_idx ON employees (ename, job, hiredate)

Create a table by copying another table

CREATE TABLE EMPLOYEES AS SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, COMM FROM EMP

To create a table with a Primary KEY

CREATE TABLE products ( product_id number not null, name varchar(50), product_cost number(5,2), product_retail number(5,2), product_type varchar(10), store_id number not null, CONSTRAINT product_pk PRIMARY KEY (product_id) )

Code to create a table

CREATE TABLE stores ( store_id number not null, city varchar(50) )

Code to build an unique index

CREATE UNIQUE INDEX emp_job_idx -- cannot create UNIQUE INDEX ON employees (job) Note: All the values in the job column must be unique for this code to work.

What's the difference between single and double quotes?

For string comparisons, we use single quote but for aliases we use double quotes

What is the substr() function?

Extracts the characters from a string, beginning at a specified start position, and through the specified number of character. Example: select substr('Hello', 2, 3) from dual Result: el Example of a length with a substring function: select length(substr('Hello', 2, 3)) from dual Result: 2

What are pipes used for?

For concatenation

INSERT INTO ... VALUES

Has to be run one at a time. Use INSERT ALL if you want insert multiple rows of data at the same time.

Faster Way to insert into a table

INSERT ALL INTO stores (store_id, city) VALUES (4, 'Philadelphia') INTO stores (store_id, city) VALUES (5, 'Boston') INTO stores (store_id, city) VALUES (6, 'Seattle') SELECT * FROM DUAL; (the SELECT is necessary for this code)

Code to insert records into a table one row at a time

INSERT INTO stores(store_id, city) VALUES (1, 'San Francisco'); INSERT INTO stores(store_id, city) VALUES (2, 'New York City'); INSERT INTO stores(store_id, city) VALUES (3, 'Chicago')

Write a query that returns the supplier names and the state in which they operate meeting the following conditions: belong in the state Georgia or Alaska the supplier id is 100 or greater than 600 the amount spent is less than 100,000 or the amount spent is 220,000

SELECT supplier_name, state FROM suppliers WHERE state IN ('Georgia', 'Alaska') AND (supplier_id = 100 OR supplier_id > 600) AND (total_spent < 100000 OR total_spent = 220000);

What does SSRS and SSIS stand for?

SQL Server Reporting Services (SSRS) and SQL Server Integration Services (SSIS)

Is SQL case sensitive?

SQL is a case insensitive language meaning both SELECT or select are fine. However, the data inside the cell is case-sensitive.

NEXT_DAY(d, c)

The first argument is the date and the second argument is a text reference to a day of the week. Both arguments are required for this function to work properly. This function returns a valid date representing the first occurrence of the c day following the date represented in d.

Which of the ERD diagram is the foreign key on?

The foreign key is always on the many-side of the relationship.

Which query is better? select * from employees where salary > 130000 and region_id in (select region_id from regions where country = 'Asia' or country = 'Canada') select * from employees where salary > 130000 and region_id in (select region_id from regions where country in ('Asia', 'Canada'))

The in one is better. The equal to one didn't work for me.