Category Archives: SQL
SQL Queries for Interview
What is the simplest SQL Query to find the second largest value?
SELECT MAX(col)
FROM table
WHERE col < (SELECT MAX(col)
FROM table)
SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);
What is the difference between UNION and UNION ALL
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
UNION Example:
SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar
Result
+-----+ | bar | +-----+ | foo | +-----+ 1 row in set (0.00 sec)
UNION ALL example:
SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar
Result
+-----+ | bar | +-----+ | foo | | foo | +-----+ 2 rows in set (0.00 sec)
Query for website site counter based on month
CREATE TABLE ipaddress(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
date VARCHAR(255),
ip VARCHAR(255));
INSERT INTO ipaddress(date, ip)
VALUES('1-1-2012', '195.165.1.2'),
('1-1-2012', '195.165.1.3'),
('12-2-2012', '195.165.1.8');
Total number of Visits should be grouped based on IP Address
SELECT COUNT(ip), MONTHNAME(STR_TO_DATE(date, '%c-%e-%Y')) as period FROM ipaddress GROUP BY period;
Query2
The first Location from second table should be added to the first table Location.
CREATE TABLE projectlist(ProjectId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
ProjectName VARCHAR(50),
Location VARCHAR(50));
CREATE TABLE LocationList(LocaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
ProjectId INT,
Location VARCHAR(50));
INSERT INTO projectlist(ProjectName)
VALUES('Project A'),
('Project B'),
('Project C'),
('Project D'),
('Project E'),
('Project F'),
('Project G'),
('Project H');
INSERT INTO LocationList(ProjectId, Location)
VALUES(1, 'Location A'),
(1, 'Location C'),
(3, 'Location B'),
(4, 'Location C'),
(5, 'Location D'),
(2, 'Location C'),
(2, 'Location B'),
(2, 'Location A'),
(6, 'Location A'),
(6, 'Location B'),
(7, 'Location B'),
(8, 'Location D'),
(8, 'Location A');
UPDATE projectlist JOIN (SELECT LocationList.ProjectID,
LocationList.Location
FROM LocationList
JOIN (SELECT LocationList.ProjectID,
min(LocaId) minLocaId
FROM LocationList
GROUP BY LocationList.ProjectID) l1
ON LocationList.LocaId = l1.minLocaID) l2 ON projectList.ProjectID = l2.ProjectID
SET ProjectList.Location = l2.Location;
Group By, Order By, Limit Which Comes First
FROM tableName WHERE more filters on the rows GROUP BY combines those rows into groups HAVING filters groups ORDER BY arranges the remaining rows/groups;
Alternate to Inner Join which prevents repetation of Records
create table fruits(fruit_id int, fruit_name VARCHAR(255));
create table type(fruit_id int, status VARCHAR(255));
INSERT INTO fruits(fruit_id, fruit_name)
values(101, 'Apple'),
(102, 'Mango'),
(103, 'Lemon'),
(104, 'Grape'),
(105, 'Orange');
INSERT INTO type(fruit_id, status)
values(101, 'Edible'),
(101, 'Sweet'),
(102, 'Edible'),
(103, 'Edible'),
(103, 'Salty'),
(103, 'Sour'),
(104, 'Sour');
When i Use Inner Join as Below the Output would be
To prevent the repetition of fruit name the queries are as follows
SELECT f.fruit_id, f.fruit_name
FROM fruits f
WHERE f.fruit_id in(SELECT DISTINCT fruit_id
FROM type);
SELECT DISTINCT t.Fruit_id, fruit_name
FROM fruits f RIGHT OUTER JOIN
type t ON F.Fruit_id = t.Fruit_id;
SELECT fruits.*
FROM fruits inner join
type on type.fruit_id = fruits.fruit_id
GROUP BY type.fruit_id;
Getting Rows from table whose status changes first time
Table Creation Script
CREATE TABLE Customers(Row_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Cust_Name VARCHAR(255),
Created_Date DATE,
Cust_Status TINYINT)
The Rows in the table are as Below
INSERT INTO Customers(Cust_Name, Created_Date, Cust_Status)
VALUES('Customer A', '20120516', 0),
('Customer B', '20120516', 0),
('Customer C', '20120516', 0),
('Customer A', '20120517', 1),
('Customer B', '20120517', 0),
('Customer C', '20120517', 0),
('Customer A', '20120520', 1),
('Customer B', '20120520', 0),
('Customer C', '20120520', 1),
('Customer A', '20120521', 0),
('Customer B', '20120521', 0),
('Customer C', '20120521', 1),
('Customer A', '20120526', 1),
('Customer B', '20120526', 1),
('Customer C', '20120526', 0),
('Customer A', '20120530', 1),
('Customer B', '20120530', 1),
('Customer C', '20120530', 0);
I want to take the rows which changes their Cust_Status from 0 to 1 only for first time.
When I run the script by giving 20120517 as parameter to where clause it should return Customer A.
When I run the script by giving 20120520 as parameter to where clause it should return Customer C.
When I run the script by giving 20120526 as parameter to where clause it should return Customer B.
SELECT C.Cust_Name
FROM Customers C
WHERE C.Created_Date = '20120526' AND
C.Cust_Status = 1 AND
NOT EXISTS (SELECT c2.Cust_Name
FROM Customers c2
WHERE c2.Cust_Name = C.Cust_Name AND
c2.Cust_Status = 1 AND
c2.Created_Date < C.Created_Date)
Left Outer Join Query
Two tables
CREATE TABLE tblEatables (
`EatId` int UNSIGNED PRIMARY AUTO_INCREMENT,
`Fruits` varchar(9) NOT NULL
) Engine=InnoDB;
CREATE TABLE tblConfirm_Eatables (
Eatables_Id INT UNSIGNED,
Edible_Status INT,
FOREIGN KEY Eatables_Id REFERENCES tblEatables (EatId)
) Engine=InnoDB;
Rows in Tables
INSERT INTO tblEatables(`EatId`, `Fruits`)
VALUES(1, 'Apples'),
(2, 'Oranges'),
(3, 'Papaya'),
(4, 'Jackfruit'),
(5, 'Pineapple'),
(6, 'Mango');
INSERT INTO tblConfirm_Eatables
VALUES(1,0),
(2,1),
(3,0),
(4,0);
The Result Should be
Fruits Apple Papaya Jackfruit Pineapple Mango
Query1
SELECT Fruits
FROM tblEatables AS E LEFT JOIN
tblConfirm_Eatables AS CE ON E.EatID = CE.Eatables_ID
WHERE CE.Edible_Status = 0 OR
CE.Edible_Status IS NULL
Query 2
SELECT e.EatId,e.Fruits
FROM @tblEatables e LEFT JOIN
@tblConfirm_Eatables ce ON e.EatId = ce.Eatbles_Id
WHERE ce.Edible_Status = 0 OR ce.Edible_Status IS Null
Query 3
SELECT fruits
FROM tblEatables
WHERE EatID NOT IN (SELECT Eatables_Id
FROM tblConfirm_Eatables
WHERE Edible_Status = 1)
Group By Status
I have Table with Columns Name, EmailId, Process, Status Like Below. Now what i would like to have is the emailId and Status of people whoes Status are closed for all the Process – Process1, process2, process 3
Input
Output
Query1
SELECT fruit_email_id
FROM Fruits AS t
GROUP BY fruit_email_id
HAVING COUNT( CASE WHEN Status 'Closed'
THEN 1
END) = 0;
Query2
SELECT t.fruit_email_id
FROM (SELECT DISTINCT fruit_email_id
FROM Fruits) AS t
LEFT JOIN Fruits AS tt ON tt.fruit_email_id = t.fruit_email_id AND
tt.Status 'Closed'
WHERE tt.fruit_email_id IS NULL;