GST Billing Software

EXERCISE 2

header image

SQL Queries

Erachana Line

BILLAPP

EXERCISE 1

Create database

CREATE DATABASE BILLAPP;

Create tables

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT PRIMARY KEY NOT NULL,

CUSTOMER_NAME VARCHAR (255),

CUSTOMER_ADD VARCHAR (255),

PHONE_NO NUMERIC (10)

);

CREATE TABLE BILL

(

BILL_NO INT PRIMARY KEY NOT NULL,

BILLDATE DATE,

CUSTOMERID INT,

BILL_AMOUNT NUMERIC (10,2),

FOREIGN KEY (CUSTOMERID)REFERENCES CUSTOMER (CUSTOMER_ID)

);

CREATE TABLE RECIEPT

(

RECNO INT PRIMARY KEY NOT NULL,

RECDATE DATE,

BILL_NO INT,

AMOUNT NUMERIC (10,2)

FOREIGN KEY (BILL_NO) REFERENCES BILL (BILL_NO)

);

Insert values

INSERT INTO CUSTOMER VALUES(1,'MEHAR','PUTTUR',9482115467);

INSERT INTO CUSTOMER VALUES(2,'CHAITHRA','MYSORE',9482515467);

INSERT INTO CUSTOMER VALUES(3,'THANMAYEE','DARBE',9482665467);

INSERT INTO CUSTOMER VALUES(4,'ADITHYA','BANGALORE',9482225467);

INSERT INTO CUSTOMER VALUES (5,'MEHAR FATHIMA','CHENNAI',9482112267);

INSERT INTO BILL VALUES (1,'2003/12/12',1,1000);

INSERT INTO BILL VALUES (2,'2003/6/12',1,2000);

INSERT INTO BILL VALUES (3,'2003/12/12',2,1000);

INSERT INTO BILL VALUES (4,'2003/6/12',2,20000);

INSERT INTO BILL VALUES (5,'2003/12/12',3,5000);

INSERT INTO BILL VALUES (6,'2003/6/12',3,20000);

INSERT INTO BILL VALUES (7,'2003/12/12',4,50000);

INSERT INTO BILL VALUES (8,'2003/6/12',4,20000);

INSERT INTO BILL VALUES (9,'2003/12/12',5,50500);

INSERT INTO BILL VALUES (10,'2003/6/12',5,20100);

INSERT INTO RECIEPT VALUES (1,'2003/12/18',1,500);

INSERT INTO RECIEPT VALUES (2,'2003/12/17',3,1500);

INSERT INTO RECIEPT VALUES (3,'2003/12/17',5,300);

INSERT INTO RECIEPT VALUES (4,'2003/12/13',7,1500);

INSERT INTO RECIEPT VALUES (5,'2003/12/16',9,5000);

query to Select bill details

SELECT CUSTOMER_NAME, PHONE_NO, BILL_NO, BILLDATE, BILL_AMOUNT

FROM BILL INNER JOIN CUSTOMER

ON BILL.CUSTOMERID=CUSTOMER.CUSTOMER_ID;

OUTPUT:

CUSTOMER_NAME

PHONE_NO

BILL_NO

BILLDATE

BILL_AMOUNT

MEHAR

9482115467

1

12-12-2003

1000

MEHAR

9482115467

2

12-06-2003

2000

CHAITHRA

9482515467

3

12-12-2003

1000

CHAITHRA

9482515467

4

12-06-2003

20000

THANMAYEE

9482665467

5

12-12-2003

5000

THANMAYEE

9482665467

6

12-06-2003

20000

ADITHYA

9482225467

7

12-12-2003

50000

ADITHYA

9482225467

8

12-06-2003

20000

MEHAR FATHIMA

9482112267

9

12-12-2003

50500

MEHAR FATHIMA

9482112267

10

12-06-2003

20100


query to Select CUSTOMER_NAME, PHONE_NO, BILL.BILL_NO, RECNO, RECDATE, AMOUNT

SELECT CUSTOMER_NAME, PHONE_NO, BILL.BILL_NO, RECNO, RECDATE, AMOUNT

FROM BILL INNER JOIN RECIEPT

ON RECIEPT.BILL_NO=BILL.BILL_NO INNER JOIN CUSTOMER ON

BILL.CUSTOMERID=CUSTOMER.CUSTOMER_ID;

CUSTOMER_NAME

PHONE_NO

BILL_NO

RECNO

RECDATE

AMOUNT

MEHAR

9482115467

1

01-01-1900

18-12-2003

500

CHAITHRA

9482515467

3

02-01-1900

17-12-2003

1500

THANMAYEE

9482665467

5

03-01-1900

17-12-2003

300

ADITHYA

9482225467

7

04-01-1900

13-12-2003

1500

MEHAR FATHIMA

9482112267

9

05-01-1900

16-12-2003

5000


query to Select Customer summery

SELECT CUSTOMER_NAME, PHONE_NO, CUSTOMER_ADD, SUM(BILL_AMOUNT) AS BILL_AMOUNT

FROM BILL INNER JOIN CUSTOMER

ON BILL.CUSTOMERID=CUSTOMER.CUSTOMER_ID

GROUP BY CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, PHONE_NO, CUSTOMER_ADD;

CUSTOMER_NAME

PHONE_NO

CUSTOMER_ADD

BILL_AMOUNT

MEHAR

9482115467

PUTTUR

18-03-1908

CHAITHRA

9482515467

MYSORE

29-06-1957

THANMAYEE

9482665467

DARBE

11-06-1968

ADITHYA

9482225467

BANGALORE

25-08-2091

MEHAR FATHIMA

9482112267

CHENNAI

16-04-2093


query to Select Name, Phone no and total receipt amount

SELECT CUSTOMER_NAME, PHONE_NO, SUM(AMOUNT)AS AMOUNT

FROM BILL INNER JOIN RECIEPT

ON RECIEPT.BILL_NO=BILL.BILL_NO INNER JOIN CUSTOMER ON

BILL.CUSTOMERID=CUSTOMER.CUSTOMER_ID

GROUP BY CUSTOMER.CUSTOMER_ID, CUSTOMER_NAME, PHONE_NO;

CUSTOMER_NAME

PHONE_NO

AMOUNT

MEHAR

9482115467

500

CHAITHRA

9482515467

1500

THANMAYEE

9482665467

300

ADITHYA

9482225467

1500

MEHAR FATHIMA

9482112267

5000


query to Select Name, total receipt amount, total bill amount and outstanding amount

SELECT CUSTOMER_NAME, SUM(BILL_AMOUNT) AS BILL_AMOUNT,

SUM(AMOUNT)AS RECIEPT_AMOUNT,

(SUM(BILL_AMOUNT)-SUM(AMOUNT)) AS OUTSTANDING

FROM BILL left JOIN CUSTOMER

ON BILL.CUSTOMERID=CUSTOMER.CUSTOMER_ID left JOIN RECIEPT ON

RECIEPT.BILL_NO=BILL.BILL_NO

GROUP BY CUSTOMER_NAME;

CUSTOMER_NAME

PHONE_NO

AMOUNT

MEHAR

9482115467

500

CHAITHRA

9482515467

1500

THANMAYEE

9482665467

300

ADITHYA

9482225467

1500

MEHAR FATHIMA

9482112267

5000


query to Select Name of the person whose outstanding amount between 1000 and 5000.

SELECT* FROM (

SELECT CUSTOMER_NAME, SUM(BILL_AMOUNT) AS BILL_AMOUNT,

SUM(AMOUNT)AS RECIEPT_AMOUNT,

(SUM(BILL_AMOUNT)-SUM(AMOUNT)) AS OUTSTANDING

FROM BILL left JOIN CUSTOMER

ON BILL.CUSTOMERID=CUSTOMER.CUSTOMER_ID left JOIN RECIEPT ON

RECIEPT.BILL_NO=BILL.BILL_NO

GROUP BY CUSTOMER_NAME) AS A

WHERE OUTSTANDING BETWEEN 1000 and 5000;

CUSTOMER_NAME

BILL_AMOUNT

RECIEPT_AMOUNT

OUTSTANDING

MEHAR

3000

500

04-11-1906

Query to demonstrate Usage of Case when condition.

SELECT CUSTOMER_ID, (CASE WHEN CUSTOMER_ID=1 THEN 'A' ELSE 'b' END) AS A FROM CUSTOMER

CUSTOMER_ID

A

1

A

2

b

3

b

4

b

5

b

Consider the following Tables -

Table - EmployeeDetails

EmpId

FullName

ManagerId

DateOfJoining

121

John Snow

321

01/31/2014

321

Walter White

986

01/30/2015

421

Kuldeep Rana

876

27/11/2016

Table - EmployeeSalary

EmpId

Project

Salary

121

P1

8000

321

P2

1000

421

P1

12000



Ques.1. Write SQL query to fetch the count of employees working in project 'P1'.
Ans. Here, we would be using aggregate function count () with the SQL where clause-

SELECT COUNT (*) FROM EmployeeSalary WHERE Project = 'P1';

Ques.2. Write SQL query to fetch employee names having salary greater than or equal to 5000 and less than or equal 10000.
Ans. Here, we will use BETWEEN in the 'where' clause to return the empId of the employees with salary satifying the required criteria and then use it as subquery to find the fullName of the employee form EmployeeDetails table.

SELECT FullName

FROM EmployeeDetails

WHERE EmpId IN

(SELECT EmpId FROM EmpolyeeSalary

WHERE Salary BETWEEN 5000 AND 10000);

Ques.3. Write SQL query to fetch project-wise count of employees sorted by project's count in descending order.
Ans. The query has two requirements - first to fetch the project-wise count and then to sort the result by that count. For project wise count, we will be using GROUPBY clause and for sorting, we will use ORDER BY clause on the alias of the project-count.

SELECT Project, count (EmpId) EmpProjectCount

FROM EmployeeSalary

GROUP BY Project

ORDER BY EmpProjectCount DESC;


Ques.4. Write a query to fetch only the first name (string before space) from the FullName column of EmployeeDetails table.
Ans. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use LOCATE method in mySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use SUBSTRING OR MID method.

mySQL- Using MID

SELECT MID(FullName, 0, LOCATE(' ',FullName)) FROM EmployeeDetails;

SQL Server-Using SUBSTRING

SELECT SUBSTRING(FullName, 0, CHARINDEX(' ',FullName)) FROM EmployeeDetails;

Also, we can use LEFT which returns the left part of a string till specified number of characters.

SELECT LEFT(FullName, CHARINDEX(' ',FullName) - 1) FROM EmployeeDetails;

Ques.5. Write query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.
Ans. Here, we can use left join with EmployeeDetail table on the left side.

SELECT E.FullName, S.Salary

FROM EmployeeDetails E LEFT JOIN EmployeeSalary S

ON E.EmpId = S.EmpId;

Ques.6. Write SQL query to fetch all the Employees who are also managers from EmployeeDetails table.
Ans. Here, we have to use Self-Join as the requirement wants us to analyze the EmployeeDetails table as two different tables, each for Employee and manager records.

SELECT DISTINCT E.FullName

FROM EmpDetails E

INNER JOIN EmpDetails M

ON E.EmpID = M.ManagerID;

Ques.7. Write SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table.
Ans. Using 'Exists'-

SELECT * FROM EmployeeDetails E

WHERE EXISTS

(SELECT * FROM EmployeeSalary S WHERE E.EmpId = S.EmpId);

Ques.8. Write SQL query to fetch duplicate records from a table.
Ans. To find duplicate records from table we can use GROUP BY on all the fields and then use HAVING clause to return only those fields whose count is greater than 1 i.e. the rows having duplicate records.

SELECT EmpId, Project, Salary, COUNT (*)

FROM EmployeeSalary

GROUP BY EmpId, Project, Salary

HAVING COUNT (*) > 1;

Ques.9. Write SQL query to remove duplicates from a table without using temporary table.
Ans. Using Group by and Having clause-

DELETE FROM EmployeeSalary

WHERE EmpId IN (

SELECT EmpId

FROM EmployeeSalary

GROUP BY Project, Salary

HAVING COUNT (*) > 1));

Ques.10. Write SQL query to fetch common records between two tables.
Ans. Using INTERSECT-

SELECT * FROM EmployeeSalary

INTERSECT

SELECT * FROM ManagerSalary

Ques.11. Write SQL query to fetch records that are present in one table but not in another table.
Ans. Using MINUS-

SELECT * FROM EmployeeSalary

MINUS

SELECT * FROM ManagerSalary

Ques.12. Write SQL query to fetch all the Employees from EmployeeDetails table who joined in Year 2016.
Ans. Using BETWEEN for the date range '01-01-2016' AND '31-12-2016'-

SELECT * FROM EmployeeSalary

WHERE DateOfJoining BETWEEN '01-01-2016' AND date '31-12-2016';

Also, we can extract year part from the joining date (using YEAR in mySQL)-

SELECT * FROM EmployeeSalary

WHERE YEAR (DateOfJoining) = '2016';

Ques.13. Write SQL query to find the 3rd highest salary from table without using TOP/limit keyword.
Ans. The below SQL query make use of correlated subquery wherein in order to find the 3rd highest salary the inner query will return the count of till we find that there are two rows that salary greater than other distinct salaries.

SELECT Salary

FROM EmployeeSalary Emp1

WHERE 2 = (

SELECT COUNT (DISTINCT ( Emp2.Salary ) )

FROM EmployeeSalary Emp2

WHERE Emp2.Salary > Emp1.Salary

)