Structured Query Language

No worries - But now you can!

No worries not only now you can write your queries but have a good understanding of SQL!!

What is SQL!!

SQL: Structured Query Language, used to access and manipulate data. SQL is not DB, is a query language.

SQL used CRUD operations to communicate with DB.

CREATE - execute INSERT statements to insert a new tuple into the relation.
READ - Read data already in the relations.
UPDATE - Modify already inserted data in the relation.
DELETE - Delete specific data points/tuple/row or multiple rows.

What is RDBMS? (Relational Database Management System)

Software that enables us to implement a designed relational model.
e.g., MySQL, MS SQL, Oracle, IBM etc.
Table/Relation is the simplest form of data storage object in R-DB.
MySQL is an open-source RDBMS, and it uses SQL for all CRUD operations

MySQL

MySQL used a client-server model, where the client is CLI or frontend that used services provided by the MySQL server.

Difference between SQL and MySQL

SQL is a Structured Query language used to perform CRUD operations in R-DB, while MySQL is an RDBMS used to store, manage and administrate DB (provided by itself) using SQL.

SQL DATA TYPES.

Refer link: w3schools

Youtube: Codehelp

In SQL DB, data is stored in the form of tables.

Data can be of different types, like INT, CHAR etc.

Size: TINY < SMALL < MEDIUM < INT < BIGINT.

Variable length Data types e.g., VARCHAR, are better to use as they occupy space equal to the actual data size. Values can also be unsigned e.g., INT UNSIGNED.

Types of SQL commands:

DDL (data definition language): defining relation schema.

CREATE: create table, DB, view.
ALTER TABLE: modification in table structure. e.g, change column datatype or add/remove columns.
DROP: delete table, DB, view.
TRUNCATE: remove all the tuples from the table.
RENAME: rename DB name, table name, column name etc.

DRL/DQL (data retrieval language/data query language): retrieve data from the tables.

SELECT

DML (data modification language): use to perform modifications in the DB

INSERT: insert data into a relation
UPDATE: update relation data.
DELETE: delete row(s) from the relation.

DCL (Data Control Language): grant or revoke authorities from users.

GRANT: access privileges to the DB
REVOKE: revoke user access privileges.

TCL (Transaction control language): to manage transactions done in the DB

START TRANSACTION: begin a transaction
COMMIT: apply all the changes and end transaction.
ROLLBACK: discard changes and end transaction.
SAVEPOINT: checkout within the group of transactions in which to rollback.

MANAGING DB (DDL)

Creation of DB

CREATE DATABASE IF NOT EXISTS db-name;
USE db-name; //need to execute to choose on which DB CREATE TABLE etc commands will be executed. //make switching between DBs possible.
DROP DATABASE IF EXISTS db-name; //dropping database.
SHOW DATABASES; //list all the DBs in the server.
SHOW TABLES; //list tables in the selected DB.

DATA RETRIEVAL LANGUAGE (DRL)

Syntax: SELECT FROM <table_name>;
Order of execution from RIGHT to LEFT.
Q. Can we use SELECT keyword without using FROM clause?

Yes, using DUAL Tables.

Dual tables are dummy tables created by MySQL, help users to do certain obvious actions without referring to user defined tables.

e.g., SELECT 55 + 11;

SELECT now();

SELECT ucase(); etc.

WHERE

Reduce rows based on given conditions.

E.g., SELECT * FROM customer WHERE age > 18;

BETWEEN

SELECT * FROM customer WHERE age between 0 AND 100;

In the above e.g., 0 and 100 are inclusive.

IN

Reduces OR conditions;
e.g., SELECT * FROM officers WHERE officer_name IN ('Msid', ‘John', ‘Wick’);

AND/OR/NOT

AND: WHERE cond'1 AND cond'2
OR: WHERE cond'1 OR cond'2 OR cond'3 ...
NOT: WHERE col_name NOT IN (1,2,3,4);

IS NULL

e.g., SELECT * FROM customer WHERE prime_status is NULL;

Pattern Searching / Wildcard (‘%’, ‘_’): LIKE

‘%’, any number of characters from 0 to n. Similar to ‘*’ asterisk in regex.
‘_’, only one character.
SELECT FROM customer WHERE name *LIKE ‘%p_’;
above ex: name that have second last char as 'p'.

ORDER BY

Sorting the data retrieved using WHERE clause.
ORDER BY DESC;
DESC = Descending and ASC = Ascending
e.g., SELECT * FROM customer ORDER BY name DESC;

GROUP BY

GROUP BY Clause is used to collect data from multiple records and group the result by one or more column. It is generally used in a SELECT statement.
Groups into category based on column given.
SELECT c1, c2, c3 FROM sample_table WHERE cond GROUP BY c1, c2, c3;

eg: SELECT dept FROM worker GROUP BY dept; => same as distict.

eg: SELECT dept COUNT(dept) FROM worker GROUP BY dept;

All the column names mentioned after SELECT statement "should be" repeated in GROUP BY, in order to successfully execute the query.
Used with aggregation functions to perform various actions.
1. COUNT() : SELECT dept COUNT(dept) FROM worker GROUP BY dept;
2. SUM(): SELECT dept SUM(salary) FROM worker GROUP BY dept; =>Total money/salary in each.
3. AVG()
4. MIN() : SELECT dept MIN(salary) FROM worker GROUP BY dept;
5. MAX(): SELECT dept MAX(salary) FROM worker GROUP BY dept;

DISTINCT

Find distinct values in the table.
SELECT DISTINCT(col_name) FROM table_name;
GROUP BY can also be used for the same
“Select col_name from table GROUP BY col_name;” same output as above DISTINCT query.
SQL is smart enough to realize that if you are using GROUP BY and not using any aggregation function, then you mean “DISTINCT”.

GROUP BY HAVING

Out of the categories made by GROUP BY, we would like to know only a particular thing (cond).
Similar to WHERE.
Select COUNT(cust_id), country from customer GROUP BY country HAVING COUNT(cust_id) > 50;

WHERE vs HAVING

Both have same function of filtering the row base on certain conditions.
WHERE clause is used to filter the rows from the table based on specified condition
HAVING clause is used to filter the rows from the groups based on the specified condition.
HAVING is used after GROUP BY while WHERE is used before GROUP BY clause.
If you are using HAVING, GROUP BY is necessary.
WHERE can be used with SELECT, UPDATE & DELETE keywords while GROUP BY used with SELECT

CONSTRAINTS (DDL)

Primary Key

PK is not null, unique and only one per table.
CREATE TABLE ORDER ( id INT PRIMARY KEY, delivery_date DATE, order_placed_date DATE, cust_id INT, FOREIGN KEY (cust_id) REFERENCES customer(id) )

Foreign Key

FK refers to PK of other table.
Each relation can having any number of FK.
CREATE TABLE ORDER ( id INT PRIMARY KEY, delivery_date DATE, order_placed_date DATE, cust_id INT,

FOREIGN KEY (cust_id) REFERENCES customer(id) );

UNIQUE

Unique, can be null, table can have multiple unique atributes.
CREATE TABLE customer ( … email VARCHAR(1024) UNIQUE, … );

CHECK

CREATE TABLE customer ( … CONSTRAINT age_check CHECK (age > 12), … );
“age_check”, can also avoid this, MySQL generates name of constraint automatically.

DEFAULT

Set default value of the column.
CREATE TABLE account ( … saving-rate DOUBLE NOT NULL DEFAULT 4.25, … );
An attribute can be PK and FK both in a table.

ALTER OPERATIONS

Changes schema

ADD

Add new column.
ALTER TABLE table_name ADD new_col_name datatype ADD new_col_name_2 datatype;
e.g., ALTER TABLE customer ADD age INT NOT NULL;

MODIFY

Change datatype of an attribute.
ALTER TABLE table-name MODIFY col-name col-datatype;
E.g., VARCHAR TO CHAR ALTER TABLE customer MODIFY name CHAR(1024);

CHANGE COLUMN

Rename column name.
ALTER TABLE table-name CHANGE COLUMN old-col-name new-col-name new-col-datatype;
e.g., ALTER TABLE customer CHANGE COLUMN name customer-name VARCHAR(1024);

DROP COLUMN

Drop a column completely.
ALTER TABLE table-name DROP COLUMN col-name;
e.g., ALTER TABLE customer DROP COLUMN middle-name;

RENAME

Rename table name itself.
ALTER TABLE table-name RENAME TO new-table-name;
e.g., ALTER TABLE customer RENAME TO customer-details;

DATA MANIPULATION LANGUAGE (DML)

INSERT

INSERT INTO table-name(col1, col2, col3) VALUES (v1, v2, v3), (val1, val2, val3);

UPDATE

UPDATE table-name SET col1 = 1, col2 = ‘abc’ WHERE id = 1;
Update multiple rows e.g.,
UPDATE student SET standard = standard + 1;
1. ON UPDATE CASCADE
2. Can be added to the table while creating constraints. Suppose there is a situation where we have two tables such that primary key of one table is the foreign key for another table. if we update the primary key of the first table then using the ON UPDATE CASCADE foreign key of the second table automatically get updated.

DELETE

DELETE FROM table-name WHERE id = 1;
DELETE FROM table-name; //all rows will be deleted.
1. DELETE CASCADE - (to overcome DELETE constraint of Referential constraints)
  1. What would happen to child entry if parent table’s entry is deleted?
    
    if deleted from parent its corresponding entry in child table is also deleted,
  2. CREATE TABLE ORDER ( order_id int PRIMARY KEY, delivery_date DATE, cust_id INT, FOREIGN KEY(cust_id) REFERENCES customer(id) ON DELETE CASCADE );
2. ON DELETE NULL - (can FK have null values?)
  
  if deleted from parent its corresponding entry in child table its FK is set Null.
  
  Can FK have null values? -> Yes if use On Delete set null.
  1. CREATE TABLE ORDER ( order_id int PRIMARY KEY, delivery_date DATE, cust_id INT, FOREIGN KEY(cust_id) REFERENCES customer(id) ON DELETE SET NULL );
    
    eg: order history remains but delete the person detail.

REPLACE

Primarily used for already present tuple in a table.
As UPDATE, using REPLACE with the help of WHERE clause in PK, then that row will be replaced.
As INSERT, if there is no duplicate data new tuple will be inserted.
REPLACE INTO student (id, class) VALUES(4, 3);
REPLACE INTO table SET col1 = val1, col2 = val2;

JOINING TABLES

All RDBMS are relational in nature, we refer to other tables to get meaningful outcomes. FK are used to do reference to other table

INNER JOIN

Returns a resultant table that has matching values from both the tables or all the tables.
SELECT column-list FROM table1 INNER JOIN table2 ON condition1 INNER JOIN table3 ON condition2 …;
1. Alias in MySQL (AS)
2. Aliases in MySQL is used to give a temporary name to a table or a column in a table for the purpose of a particular query. It works as a nickname for expressing the tables or column names. It makes the query short and neat.
3. SELECT col_name AS alias_name FROM table_name;
4. SELECT col_name1, col_name2,... FROM table_name AS alias_name;

OUTER JOIN

LEFT JOIN

This returns a resulting table that all the data from left table and the matched data from the right table.
SELECT columns FROM table LEFT JOIN table2 ON Join_Condition;

RIGHT JOIN

This returns a resulting table that all the data from right table and the matched data from the left table.
SELECT columns FROM table RIGHT JOIN table2 ON join_cond;

FULL JOIN

This returns a resulting table that contains all data when there is a match on left or right table data.
Emulated in MySQL using LEFT and RIGHT JOIN.
LEFT JOIN UNION RIGHT JOIN.
SELECT columns FROM table1 as t1 LEFT JOIN table2 as t2 ON t1.id = t2.id UNION SELECT columns FROM table1 as t1 RIGHT JOIN table2 as t2 ON t1.id = t2.id;
UNION ALL, can also be used this will duplicate values as well while UNION gives unique values.

CROSS JOIN

This returns all the cartesian products of the data present in both tables. Hence, all possible variations are reflected in the output.
Used rarely in practical purpose.
Table-1 has 10 rows and table-2 has 5, then resultant would have 50 rows.
SELECT column-lists FROM table1 CROSS JOIN table2;

SELF JOIN

It is used to get the output from a particular table when the same table is joined to itself.
Used very less.
Emulated using INNER JOIN.
SELECT columns FROM table as t1 INNER JOIN table as t2 ON t1.id = t2.id;

Join without using join keywords.

SELECT \ FROM table1, table2 WHERE condition;*
e.g., SELECT artist_name, album_name, year_recorded FROM artist, album WHERE artist.id = album.artist_id;

SET OPERATIONS.

Used to combine multiple select statements. Always gives distinct rows.

UNION

Combines two or more SELECT statements.
SELECT FROM table1 UNION SELECT FROM table2;
Number of column, order of column must be same for table1 and table2.

INTERSECT

Returns common values of the tables.
Emulated.
SELECT DISTINCT column-list FROM table-1 INNER JOIN table-2 USING(join_cond);
SELECT DISTINCT * FROM table1 INNER JOIN table2 ON USING(id);

MINUS

This operator returns the distinct row from the first table that does not occur in the second table.
Emulated.
SELECT column_list FROM table1 LEFT JOIN table2 ON condition WHERE table2.column_name IS NULL;
e.g., SELECT id FROM table-1 LEFT JOIN table-2 USING(id) WHERE table-2.id IS NULL;

SUB QUERIES

Outer query depends on inner query. Alternative to joins. Nested queries.

SELECT column_list (s) FROM table_name WHERE column_name OPERATOR (SELECT column_list (s) FROM table_name [WHERE]);

e.g., SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table1);

Sub queries exist mainly in 3 clauses

Inside a WHERE clause.
Inside a FROM clause.
Inside a SELECT clause

Subquery using FROM clause

SELECT MAX(rating) FROM (SELECT FROM movie WHERE country = ‘India’*) as temp;

Subquery using SELECT

SELECT (SELECT column_list(s) FROM T_name WHERE condition), columnList(s) FROM T2_name WHERE condition;

Derived Subquery

SELECT columnLists(s) FROM (SELECT columnLists(s) FROM table_name WHERE [condition]) as new_table_name;

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.

MySQL VIEWS
1. A view is a database object that has no values. Its contents are based on the base table. It contains rows and columns similar to the real table.
2. In MySQL, the View is a virtual table created by a query by joining one or more tables. It is operated similarly to the base table but does not contain any data of its own.
3. The View and table have one main difference that the views are definitions built on top of other tables (or views). If any changes occur in the underlying table, the same changes reflected in the View also.
4. CREATE VIEW view_name AS SELECT columns FROM tables [WHERE conditions];
5. ALTER VIEW view_name AS SELECT columns FROM table WHERE conditions;
6. DROP VIEW IF EXISTS view_name;
7. CREATE VIEW Trainer AS SELECT c.course_name, c.trainer, t.email FROM courses c, contact t WHERE c.id = t.id; NOTE: We can also import/export table schema from files (.csv or json)

Drink Some WAAATUHH cuz next we are about to do some INTERVIEWRS FAVOURITE QUERIES!!!!

Drink Some WAAATUHH cuz next we are about to do some INTERVIEWRS FAVOURITE QUERIES!!!!

Creating Database

CREATE DATABASE ORG;

SHOW DATABASES;

USE ORG;

CREATE TABLE Worker ( WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY INT(15), JOINING_DATE DATETIME, DEPARTMENT CHAR(25) );

INSERT INTO Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'), (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'), (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'), (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'), (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'), (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'), (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'), (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');

SELECT * FROM Title;

CREATE TABLE Bonus ( WORKER_REF_ID INT, BONUS_AMOUNT INT(10), BONUS_DATE DATETIME, FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID) ON DELETE CASCADE );

INSERT INTO Bonus (WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES (001, 5000, '16-02-20'), (002, 3000, '16-06-11'), (003, 4000, '16-02-20'), (001, 4500, '16-02-20'), (002, 3500, '16-06-11');

CREATE TABLE Title ( WORKER_REF_ID INT, WORKER_TITLE CHAR(25), AFFECTED_FROM DATETIME, FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID) ON DELETE CASCADE );

INSERT INTO Title (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES (001, 'Manager', '2016-02-20 00:00:00'), (002, 'Executive', '2016-06-11 00:00:00'), (008, 'Executive', '2016-06-11 00:00:00'), (005, 'Manager', '2016-06-11 00:00:00'), (004, 'Asst. Manager', '2016-06-11 00:00:00'), (007, 'Executive', '2016-06-11 00:00:00'), (006, 'Lead', '2016-06-11 00:00:00'), (003, 'Lead', '2016-06-11 00:00:00');

Tables in org db will look like this :

Lets ANSWER some INTERESTING Queries shall we :

Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>.

select first_name AS WORKER_NAME from worker;

Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.

select UPPER(first_name) from worker;

Q-3. Write an SQL query to fetch unique values of DEPARTMENT from Worker table.

SELECT distinct department from worker;

Q-4. Write an SQL query to print the first three characters of FIRST_NAME from Worker table.

select substring(first_name, 1, 3) from worker;

Q-5. Write an SQL query to find the position of the alphabet (‘b’) in the first name column ‘Amitabh’ from Worker table.

select INSTR*(first_name, 'B') from worker where first_name = 'Amitabh';*

Q-6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.

select RTRIM(first_name) from worker;

Q-7. Write an SQL query to print the DEPARTMENT from Worker table after removing white spaces from the left side.

select LTRIM(first_name) from worker;

Q-8. Write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length.

select distinct department, LENGTH(department) from worker;

Q-9. Write an SQL query to print the FIRST_NAME from Worker table after replacing ‘a’ with ‘A’.

select REPLACE(first_name, 'a', 'A') from worker;

Q-10. Write an SQL query to print the FIRST_NAME and LAST_NAME from Worker table into a single column COMPLETE_NAME. -- A space char should separate them.

select CONCAT(first_name, ' ', last_name) AS COMPLETE_NAME from worker;

Q-11. Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.

select * from worker ORDER by first_name;

Q-12. Write an SQL query to print all Worker details from the Worker table order by -- FIRST_NAME Ascending and DEPARTMENT Descending.

select * from worker order by first_name, department DESC;

Q-13. Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker table.

select * from worker where first_name IN ('Vipul', 'Satish');

Q-14. Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker table.

select * from worker where first_name NOT IN ('Vipul', 'Satish');

Q-15. Write an SQL query to print details of Workers with DEPARTMENT name as “Admin*”.

select * from worker where department LIKE 'Admin%';

Q-16. Write an SQL query to print details of the Workers whose FIRST_NAME contains ‘a’.

select * from worker where first_name LIKE '%a%';

Q-17. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.

select * from worker where first_name LIKE '%a';

Q-18. Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets.

select * from worker where first_name LIKE '_h';

Q-19. Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.

select * from worker where salary between 100000 AND 500000;

Q-20. Write an SQL query to print details of the Workers who have joined in Feb’2014.

select * from worker where YEAR(joining_date) = 2014 AND MONTH(joining_date) = 02;

Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.

select department, count(*) from worker where department = 'Admin';

Q-22. Write an SQL query to fetch worker full names with salaries >= 50000 and <= 100000.

select concat(first_name, ' ', last_name) from worker where salary between 50000 and 100000;

Q-23. Write an SQL query to fetch the no. of workers for each department in the descending order.

select department, count(worker_id) AS no_of_worker from worker group by department ORDER BY no_of_worker desc;

Q-24. Write an SQL query to print details of the Workers who are also Managers.

select w. from worker as w inner join title as t on w.worker_id = t.worker_ref_id where *t.worker_title = 'Manager';

Q-25. Write an SQL query to fetch number (more than 1) of same titles in the ORG of different types.

select worker_title, count() as count from title **group by worker_title having count > 1;*

Q-26. Write an SQL query to show only odd rows from a table.

select from worker where MOD (WORKER_ID, 2) != 0;*

select * from worker where MOD (WORKER_ID, 2) <> 0;

Q-27. Write an SQL query to show only even rows from a table.

select * from worker where MOD (WORKER_ID, 2) = 0;

Q-28. Write an SQL query to clone a new table from another table.

CREATE TABLE worker_clone LIKE worker;

INSERT INTO worker_clone select from worker;*

select * from worker_clone;

Q-29. Write an SQL query to fetch intersecting records of two tables.

select worker. from worker inner join worker_clone **using(worker_id)*;

Q-30. Write an SQL query to show records from one table that another table does not have. (MINUS)

select worker. from worker left join worker_clone using(worker_id) **WHERE worker_clone.worker_id is NULL*;

Q-31. Write an SQL query to show the current date and time. -- DUAL

select curdate();

select now();

Q-32. Write an SQL query to show the top n (say 5) records of a table order by descending salary.

select * from worker order by salary desc LIMIT 5;

Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.

select * from worker order by salary desc LIMIT 4,1;

//Limit clause has two components, First component is to skip number of rows from top and second component is display number of rows we want.

//SELECT DISTINCT Salary FROM tblemployee ORDER BY Salary DESC LIMIT 1 OFFSET (n-1)

Q-34. Write an SQL query to determine the 5th highest salary without using LIMIT keyword.

select salary from worker w1 WHERE 4 = ( SELECT COUNT(DISTINCT (w2.salary)) from worker w2 where w2.salary >= w1.salary );

Q-35. Write an SQL query to fetch the list of employees with the same salary.

select w1.* from worker w1, worker w2 where w1.salary = w2.salary and w1.worker_id != w2.worker_id;

Q-35. Write an SQL query to fetch the list of employees with the same salary.

select w1.* from worker w1, worker w2 where w1.salary = w2.salary and w1.worker_id != w2.worker_id;

Q-36. Write an SQL query to show the second highest salary from a table using sub-query.

select max(salary) from worker where salary not in (select max(salary) from worker);

Q-37. Write an SQL query to show one row twice in results from a table.

select from worker UNION ALL select from worker ORDER BY worker_id;

Q-38. Write an SQL query to list worker_id who does not get bonus.

select worker_id from worker where worker_id not in (select worker_ref_id from bonus);

Q-39. Write an SQL query to fetch the first 50% records from a table.

select from worker where worker_id <= ( *select count(worker_id)/2 from worker);

Q-40. Write an SQL query to fetch the departments that have less than 4 people in it.

select department, count(department) as depCount from worker group by department having depCount < 4;

Q-41. Write an SQL query to show all departments along with the number of people in there.

select department, count(department) as depCount from worker group by department;

Q-42. Write an SQL query to show the last record from a table.

select from worker where worker_id = (*select max(worker_id) from worker);

Q-43. Write an SQL query to fetch the first row of a table.

select from worker where worker_id = (*select min(worker_id) from worker);

Q-44. Write an SQL query to fetch the last five records from a table.

(select * from worker order by worker_id desc limit 5) order by worker_id;

Q-45. Write an SQL query to print the name of employees having the highest salary in each department.

select w.department, w.first_name, w.salary from (select max(salary) as maxsal, department from worker group by department) temp inner join worker w on temp.department = w.department and temp.maxsal = w.salary;

Q-46. Write an SQL query to fetch three max salaries from a table using co-related subquery.

select distinct salary from worker w1 where 3 >= (select count(distinct salary) from worker w2 where w1.salary <= w2.salary) order by w1.salary desc;

select distinct salary from worker order by salary desc limit 3;

Q-47. Write an SQL query to fetch three min salaries from a table using co-related subquery.

select distinct salary from worker w1 where 3 >= (select count(distinct salary) from worker w2 where w1.salary >= w2.salary) order by w1.salary desc;

Q-48. Write an SQL query to fetch nth max salaries from a table.

select distinct salary from worker w1 where n >= (select count(distinct salary) from worker w2 where w1.salary <= w2.salary) order by w1.salary desc;

Q-49. Write an SQL query to fetch departments along with the total salaries paid for each of them.

select department , sum(salary) as depSal from worker group by department order by depSal desc;

Q-50. Write an SQL query to fetch the names of workers who earn the highest salary.

select first_name, salary from worker where salary = (select max(Salary) from worker);

YAAH!! You made it to the end!! Hope you like it, NOW you can say I can write SQL queries proficiently.

THANKS YOU ! YOU TAKE YOUR PRECIOUS TIME TO READ THIS BLOG!

All you need to know about SQL + Interviewers favourite queries!