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, 'Arack', 'Sebastian', 80000, '14-06-11 09.00.00', 'Admin'),
(003, 'Tek', 'Ming', 300000, '14-02-20 09.00.00', 'HR'),
(004, 'Peter', 'Parker', 500000, '14-02-20 09.00.00', 'Admin'),
(005, 'Ma', 'Zhong', 500000, '14-06-11 09.00.00', 'Admin'),
(006, 'Vladimir', 'Nikols', 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');
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');
/*select * from Worker;
select * from Bonus;
select * from Title;*/
/*
-- Data seeding ends
-- Now answer the following Questions
--Q-1. Write an SQL query to fetch “FIRST_NAME” from the Worker table using the alias name <WORKER_NAME>.*/
/*Select FIRST_NAME AS WORKER_NAME from Worker;*/
/*--Q-2. Write an SQL query to fetch “FIRST_NAME” from the Worker table in upper case.*/
/*Select upper(FIRST_NAME) from Worker;*/
/*--Q-3. Write an SQL query to fetch unique values of DEPARTMENT from the Worker table.*/
/*Select distinct DEPARTMENT from Worker;*/
/*--Q-4. Write an SQL query to print the first three characters of FIRST_NAME from the Worker table.*/
/*Select substring(FIRST_NAME,1,3) from Worker;*/
/*--Q-5. Write an SQL query to find the position of the alphabet (‘a’) in the first name column ‘Arack’ from the Worker table.*/
/*Select INSTR(FIRST_NAME, 'A') from Worker where FIRST_NAME = 'Arack';*/
/*--Q-6. Write an SQL query to print the FIRST_NAME from the 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 the Worker table after removing white spaces from the left side.*/
/*Select LTRIM(DEPARTMENT) from Worker;*/
/*--Q-8. Write an SQL query that fetches the unique values of DEPARTMENT from the Worker table and prints its length.*/
/*Select distinct length(DEPARTMENT) from Worker;*/
/*--Q-9. Write an SQL query to print the FIRST_NAME from the 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 the 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 asc;*/
/*--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 asc,DEPARTMENT desc;*/
/*--Q-13. Write an SQL query to print details for Workers with the first names “Ma” and “Satish” from the Worker table.*/
/*Select * from Worker where FIRST_NAME in ('Ma','Satish');*/
/*--Q-14. Write an SQL query to print details of workers excluding first names, “Ma” and “Satish” from the Worker table.*/
/*Select * from Worker where FIRST_NAME not in ('Ma','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 joined in Feb’2014.*/
/*Select * from Worker where year(JOINING_DATE) = 2014 and month(JOINING_DATE) = 2;*/
/*--Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.*/
/*SELECT COUNT(*) FROM worker WHERE DEPARTMENT = 'Admin';*/
/*--Q-22. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.*/
/*SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary
FROM worker
WHERE WORKER_ID IN
(SELECT WORKER_ID FROM worker
WHERE Salary BETWEEN 50000 AND 100000);*/
/*--Q-23. Write an SQL query to fetch the no. of workers for each department in descending order.*/
/*SELECT DEPARTMENT, count(WORKER_ID) No_Of_Workers
FROM worker
GROUP BY DEPARTMENT
ORDER BY No_Of_Workers DESC;*/
/*--Q-24. Write an SQL query to print details of the Workers who are also Managers.*/
/*SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE
FROM Worker W
INNER JOIN Title T
ON W.WORKER_ID = T.WORKER_REF_ID
AND T.WORKER_TITLE in ('Manager');*/
/*--Q-25. Write an SQL query to fetch duplicate records having matching data in some fields of the Title table.*/
/*SELECT WORKER_TITLE, AFFECTED_FROM, COUNT(*)
FROM Title
GROUP BY WORKER_TITLE, AFFECTED_FROM
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;*/
/*--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 WorkerClone LIKE Worker;
INSERT INTO WorkerClone SELECT * FROM Worker;
select * from WorkerClone;*/
/*--Q-33. Write an SQL query to determine the nth (say n=5) highest salary from the Worker Table.*/
SELECT /*Salary FROM Worker ORDER BY Salary DESC LIMIT 4,1;*/
/*--Q-34. Write an SQL query to determine the 5th highest salary without using the TOP or limit method.*/
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.
--Q-36. Write an SQL query to show the second-highest salary from a table.
--Q-37. Write an SQL query to show one row twice in the results from a table.
--Q-38. Write an SQL query to fetch intersecting records of two tables.
--Q-39. Write an SQL query to fetch the first 50% of records from a table.
--Q-40. Write an SQL query to fetch the departments that have less than five people in them.
--Q-41. Write an SQL query to show all departments along with the number of people in there.
--Q-42. Write an SQL query to show the last record from a table.
--Q-43. Write an SQL query to fetch the first row of a table.
--Q-44. Write an SQL query to fetch the last five records from a table.
--Q-45. Write an SQL query to print the name of employees having the highest salary in each department.
--Q-46. Write an SQL query to fetch three max salaries from a table.
--Q-47. Write an SQL query to fetch three min salaries from a table.
--Q-48. Write an SQL query to fetch nth max salaries from a table.
--Q-49. Write an SQL query to fetch departments along with the total salaries paid for each of them.
--Q-50. Write an SQL query to fetch the names of workers who earn the highest salary.*/ Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;