6/10 - SQL Query How To Questions

JOIN/SUBQUERY

**Both are used to combine data from different tables into a single result set. JOIN: can Select from either of the tables; it's faster. SUBQUERY: can Select only from first table; it's slower.

You have two SQL tables! The first one is called employees and it contains the employee names, the unique employee ids and the department names of a company. Sample: department_name employee_id employee_name Sales 123 John Doe Sales 211 Jane Smith HR 556 Billy Bob Sales 711 Robert Hayek Marketing 235 Edward Jorgson Marketing 236 Christine Packard ... ... ... The second one is named salaries. It holds the same employee names and the same employee ids - and the salaries for each employee. Sample: salary employee_id employee_name 500 123 John Doe 600 211 Jane Smith 1000 556 Billy Bob 400 711 Robert Hayek 1200 235 Edward Jorgson 200 236 Christine Packard ... ... ... The company has 546 employees, so both tables have 546 rows. Print every department where the average salary per employee is lower than $500!

1. First JOIN the two tables: SELECT * FROM employees JOIN salaries ON employees.employee_id = salaries.employee_id Watch out! Use the employee_id column - not the employee_name. (You can always have two John Does at a company, but the employee id is unique!) 2. Then use an AVG() function with a GROUP BY clause — and replace the * with the appropriate columns. (Just like in the first task.) 3. And the last step is to use a HAVING clause to filter by the result of the AVG() function. (Remember: WHERE is not good here because it would be initiated before the AVG() function.)Watch out: in the HAVING line, you can't refer to the alias - you have to use the whole function itself again!

Difference DELETE/TRUNCATE/DROP:

1.DELETE: DML;removes some or all rows;it does not free the space containing the table; the transaction log will still have the deleted rows;slower than TRUNCATE 2.TRUNCATE: DDL;removes all the rows from the table;frees up the space containing the table; no 'WHERE' clause. 3.DROP: removes table from the database; cannot be rolled up.

WHERE/HAVING:

1.WHERE: filters rows; works on row's data, not on aggregated data. 2.HAVING: works on aggregated data.

DELETE Duplicate Rows From a Table option 2 Using AutoID:

ALTER TABLE employee ADD auto.id INT IDENTITY(1,1) DELETE * FROM employee WHERE auto_id NOT IN (SELECT MIN(auto_id) FROM employee GROUP BY emp_id, emp_name)

Remove Duplicates:

DELETE FROM Employees WHERE Emp.id IN(SELECT Emp.id FROM Employee GROUP BY Dept HAVING COUNT(*)>1)

DELETE Duplicate Rows From a Table option 3 Using Row_Number():

DELETE FROM(SELECT * row_number() OVER(PARTITION BY id ORDER BY id) AS rn WHERE rn>1

GROUP BY/ ORDER BY:

GROUP BY: used with aggregate functions; follows WHERE clause. WHERE clause cannot be used after GROUP BY. HAVING: is used after GROUP BY ORDER BY: sorts result sets ASC and DESC.

IN/EXISTS:

IN = Mutiple; compare one value to several values; IN Big outer query and small inner query. EXISTS: returns True or False, tells you whether a query returned any results. small outer query and Big inner query.

JOIN/UNION:

JOIN: merges columns; combines rows from 2 or more tables based on related common column between them. UNION: combines rows; not necessary to have the same column name; but number of columns and datatype of columns should be the same.

Fetch details of employees with address as "DELHI(DEL)"

SELECT * FROM Employees WHERE address LIKE 'DELHI(DEL)%';

Print details of employees whose salary lie BETWEEN 10,000 AND 50,000:

SELECT * FROM Employees WHERE Salary BETWEEN 10,000 AND 50,000.

Fetch common records between 2 tables:

SELECT * FROM Table1 INTERSECT SELECT * FROM Table2

Create empty table w/same structure as another table:

SELECT * INTO Table2 FROM Table1 WHERE 1=0;

How to create a Table from an existing Table:

SELECT * INTO new_table FROM old_table WHERE 1=0; [Where clause gives a false condition so no rows will be inserted only columns]

Retrieve emp first_name and last_name in a single column as "full name", first_name, last_name, separated with space:

SELECT CONCAT(emp_firstname, ' ', emp_lastname) AS 'full name' FROM Employees;

You work for a startup that makes an online presentation software. You have an event log that records every time a user inserted an image into a presentation. (One user can insert multiple images.) The event_log SQL table looks like this: user_id event_date_time 7494212 1535308430 7494212 1535308433 1475185 1535308444 6946725 1535308475 6946725 1535308476 6946725 1535308477 ... ... ...and it has over one billion rows. Note: If the event_date_time column's format doesn't look familiar, google "epoch timestamp"! Write an SQL query to find out how many users inserted more than 1000 but less than 2000 images in their presentations!

SELECT COUNT(*) FROM (SELECT user_id, COUNT(event_date_time) AS image_per_user FROM event_log GROUP BY user_id) AS image_per_user WHERE image_per_user < 2000 AND image_per_user > 1000; The trick in this task is that you had to use the COUNT() function two times: first, you had to count the number of images per user, then the number of users (who fulfill the given condition). The easiest way to do that is to use a subquery. 1. Write the inner query first! Run a simple COUNT() function with a GROUP BY clause on the event_log table. 2. Make sure that you create an alias for the subquery (AS image_per_user). It's a syntax requirement in SQL. 3. Eventually, in an outer query, apply a WHERE filter and a COUNT() function on the result of the subquery.

Find number of employees working in department 'ABC':

SELECT COUNT(*) FROM Employees WHERE Dept_name = 'ABC';

DELETE Duplicate Rows From a Table option 1 Using Temp Tbl:

SELECT DISTINCT * INTO new_table FROM old_table DELETE * FROM old_table INSERT INTO old_table SELECT * FROM new_table DROP TABLE new_table

Fetch Duplicate Records from a Table:

SELECT Emp.id, Dept, Count(*) FROM Employees GROUP BY Emp.id, Dept HAVING COUNT(*)>1

Find 2nd Highest Salary option 1. MAX:

SELECT MAX(Salary) FROM Employee WHERE Salary<(SELECT MAX(Salary) FROM Employee);

Fetch only 1st Name from Full Name column:

SELECT SUBSTRING (Full Name,0,Charindex(' ',full_name)) FROM Employees

Find 2nd Highest Salary option 2. LIMIT:

SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT2) AS Emp ORDER BY Salary LIMIT 1;

Find 2nd Highest Salary option 3. TOP:

SELECT TOP 1 Salary FROM(SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS Emp1 ORDER BY Salary ASC;

Fetch Top N records:

SELECT Top N* FROM Employees ORDER BY Salary DESC;

Fetch Emp Name in UPPER Case & use alias:

SELECT emp_name AS UPPER(emp.first_name) FROM Employees;

Retrieve emp_position along with total salariess paid for each of them:

SELECT emp_position, SUM(Salary) FROM employees GROUP BY emp_position;

Find names of employee starting with 'A':

SELECT first_name FROM Employees WHERE first_name LIKE 'A%' move insurance

Let's say you have two SQL tables: authors and books.The authors dataset has 1M+ rows; here's the first six rows: author_name book_name author_1 book_1 author_1 book_2 author_2 book_3 author_2 book_4 author_2 book_5 author_3 book_6 ... ... The books dataset also has 1M+ rows and here's the first six: book_name sold_copies book_1 1000 book_2 1500 book_3 34000 book_4 29000 book_5 40000 book_6 4400 ... ... Create an SQL query that shows the TOP 3 authors who sold the most books in total!

The solution code is: SELECT authors.author_name, SUM(books.sold_copies) AS sold_sum FROM authors JOIN books ON books.book_name = authors.book_name GROUP BY authors.author_name ORDER BY sold_sum DESC LIMIT 3; And here is a short explanation: 1. First you have to initiate the JOIN. I joined the two tables by using: SELECT * FROM authors JOIN books ON books.book_name = authors.book_name; 2. After that, I used a SUM() function with a GROUP BY clause. This means that in the SELECT statement I had to replace the * with the author_name and sold_copies columns. (It's not mandatory to indicate from which table you are selecting the columns, but it's worth it. That's why I used authors.author_name and books.sold_copies.) 3. Eventually, I ORDERed the results in DESCending order. (Just for my convenience, I also renamed the sum column to sold_sum using the AS sold_sum method in the SELECT statement.)

UNION/UNION ALL:

UNION: removes duplicate records; combines result set of 20 or more Select Statements- each Select statement must have: 1. same number of columns 2. columns much have similar databases. 3. columns must be in same order. UNION ALL: does not remove duplicates.

Increase income of all employees by 5% in a Table:

UPDATE Employees SET income=income + (income * 5/100);

DELETE Duplicate Rows From a Table option 4 Using CTE:

WITH CTE AS (SELECT row_number() OVER(PARTITION BY id) AS rn FROM Table) DELETE FROM CTE WHERE rn>1 --used as temporary result set. --lasts only for the duration of the query.