STRUCTURED QUERY LANGUAGE (SQL)

Generating Electricity bill

Field Name Type
rr_no varchar2(10)
cust_name varchar2(25)
bill_date date
units number(4)
1. Insert 10 records into the table.
2. Check the structure of the table.
3. Add 2 new fields to the table
Bill_amt number (8, 2) and due date – date.
4. Compute the bill amount for each customer as per
the following rules:
Minimum amount – Rs 50/-
For first 100 units – Rs 4.50/- per unit.
More than 100 units – Rs 5.50/- per unit.
5. Compute the due date as bill date + 15 days.
6. List all the bills generated
Command for creating the table:
SQL > CREATE TABLE KEB
(
rr_no varchar2 (10),
cust_name varchar2 (25),
bill_date date,
units number (4)
);
Command to display the structure:
SQL > DESCRIBE KEB;

Command to INSERT records into the table:

SQL > INSERT INTO KEB
VALUES („E1001‟,‟SACHIN‟,‟01-NOV-2016‟, 96);

Command to add 2 new fields bill amount and due date:

SQL > ALTER TABLE KEB
ADD (BILL_AMT NUMBER (8,2), DUE_DATE DATE);

Command to compute bill amount as per the following rules:

Minimum amount Rs 50/-
For first 100 units Rs 4.50/- per unit.
More than 100 units Rs 5.50 /- per unit
Command:
SQL > UPDATE KEB SET BILL_AMT = 50;
SQL > UPDATE KEB SET BILL_AMT
= BILL_AMT + UNITS * 4.50 WHERE UNITS <=100;
SQL> UPDATE KEB SET BILL_AMT =
BILL_AMT +100 *4.50 + (UNITS -100) * 5.50
WHERE UNITS >100;

Command to compute due date as bill_date + 15 days

SQL > UPDATE KEB SET DUE_DATE = BILL_DATE + 15;

Command to list the contents of the table:

SQL > SELECT * FROM KEB;
 CREATING STUDENT DATABASE AND
COMPUTE THE RESULT

FIELD NAME TYPE

STUDENT_ID NUMBER(4)
STUDENT_NAME VARCHAR2(25)
ENGLISH NUMBER(3)
KANNADA NUMBER(3)
PHYSICS NUMBER(3)
CHEMISTRY NUMBER(3)
MATHEMATICS NUMBER(3)
COMPUTER_SCIENCE NUMBER(3)

1. Add 10 student records into the table.

2. Display the description of the fields in the table using
disc command.
3. Alter the table and calculate total and percentage marks.
4. Compute the result as “PASS” or “FAIL” by checking if the
student has scored more than 35 marks in each subject.
5. List the contents of the table.
6. Retrieve only student id and student name of all the students.
7. List the students who have result as pass.
8. List the students who have result as fail.
9. Count the number of students who have passed.
10. Count the number of students who have failed.
11.List the students who have percentage greater than 60.
12.Sort the table according to the order of student_name.
COMMAND TO CREATE THE TABLE
CREATE TABLE STUDENT
(
student_id number (4),
student_name varchar2 (25),
english number (3),
kannada number (3),
physics number (3),
chemistry number (3),
mathematics number (3),
computer_science number (3)
);

Command to display the structure

SQL > DESCRIBE STUDENT;

Command for adding records (add 10 records) into the table:

SQL> INSERT INTO STUDENT

VALUES (6821,‟SACHIN‟, 65, 89, 56, 39, 81, 69);

Command to add total and percentage marks:

SQL> ALTER TABLE STUDENT

ADD (total number (3), per_marks number (6, 2));
Command to calculate total and percentage marks:

SQL> UPDATE STUDENT SET TOTAL =

ENGLISH + KANNADA +PHYSICS +
CHEMISTRY+MATHEMATICS+COMPUTER_SCIENCE;
SQL> UPDATE STUDENT SET PER_MARKS =
(TOTAL * 100) / 600;

Command to list the contents of the table:

SQL> SELECT * FROM STUDENT;

Command to compute result as pass or fail:

SQL> ALTER TABLE STUDENT

ADD (result varchar2 (5));
SQL> UPDATE STUDENT SET RESULT =
„PASS‟ WHERE ENGLISH > 34 AND KANNADA > 34
AND PHYSICS > 34 AND CHEMSITRY > 34 AND
MATHEMATICS > 34 AND COMPUTER_SCIENCE > 34;
SQL> UPDATE STUDENT SET RESULT =
„FAIL‟ WHERE ENGLISH < 35 OR KANNADA < 35
OR PHYSICS < 35 OR CHEMSITRY < 35 OR
MATHEMATICS < 35 OR COMPUTER_SCIENCE < 35;
Command to retrieve all the records of the table:
SQL> SELECT * FROM STUDENT;
Command to retrieve only student id and student name of all the
students:
SQL> SELECT student_id, student_name from STUDENT;
Command to list all the students who have passed AND failed:
SQL> SELECT * FROM STUDENT WHERE RESULT = „PASS‟;
SQL> SELECT * FROM STUDENT WHERE RESULT = „FAIL‟;
Command to count number of students who have passed AND
failed:
SQL> SELECT COUNT (*) FROM STUDENT
WHERE RESULT = „PASS‟;
SQL> SELECT COUNT (*) FROM STUDENT
WHERE RESULT = „FAIL‟;
Command to list all the students who have percentage >= 60:
SQL> SELECT * FROM STUDENT
WHERE PER_MARKS >=60;
Command to SORT the table according to the order of
student_name:
SQL >SELECT * FROM STUDENT ORDER BY student_name;
***************************
 EMPLOYEE DATABASE
GENERATE EMPLOYEE DETAILS AND COMPUTE
SALARY BASED ON DEPARTMENT
CREATE EMPLOYEE TABLE WITH THE FOLLOWING DETAILS
FIELD NAME DATA TYPE DESCRIPTION
emp_id number(4) Employee‟s identification number
dept_id Number(2) Department‟s identification number
emp_name varchar2(25) Employee name
emp_salary Number(5) Salary of an employee

CREATE DEPARTMENT TABLE WITH THE FOLLOWING DETAILS

FIELD NAME DATA TYPE DESCRIPTION
dept_id number(2) Departments identification number
dept_name varchar2(20) Department name
supervisor varchar2(20) Head of the department
Assume department names and their identification codes as
PURCHASE (01), ACCOUNTS (02), SALES (03) AND
MARKETING (04).

1. Enter 10 records for employee table and 4 records for department table.
2. List all employees who work for accounts department.
3. How many employees work for accounts department?
4. What is the minimum, maximum and average salary of employees
working for accounts department?
5. List all employees working for a particular supervisor.
6. Retrieve the department names for each department where only one
employee work.
7. Increase the salary for all the employees in the sales department by 15%.
8. Add a new column to the employee table – bonus number (5) and
compute 5% of salary to the said field.
9. Delete all the employees in the marketing department.
 CREATING EMPLOYEE TABLE

create table employee

(
emp_id number (4),
dept_id number (2),
emp_name varchar2 (25),
emp_salary number (5)
);
CREATING DEPARTMENT TABLE

create table department

(
dept_id number (2),
dept_name varchar2 (20),
supervisor varchar2 (20)
);

COMMAND TO INSERT (INSERT 10 RECORDS) DATA TO EMPLOYEE TABLE

SQL> INSERT INTO EMPLOYEE VALUES (1561, 01,‟SACHIN‟, 12000);

COMMAND TO INSERT (INSERT 4 RECORDS) DATA TO DEPARTMENT TABLE
SQL > INSERT INTO DEPARTMENT VALUES (01,‟PURCHASE‟,‟DRAVID‟);
INSERT INTO DEPARTMENT VALUES (02, „ACCOUNTS‟, „KUMBLE‟);
INSERT INTO DEPARTMENT VALUES (03, „SALES‟, „DHONI‟);
INSERT INTO DEPARTMENT VALUES (04, „MARKETING‟, „VIRAT‟);
COMMAND TO LIST ALL EMPLOYEES FROM EMPLOYEE AND
DEPARTMENT TABLE:
SQL > SELECT * FROM EMPLOYEE;
SQL > SELECT * FROM DEPARTMENT;
COMMAND TO LIST ALL EMPLOYEES WHO WORK FOR ACCOUNTS DEPT
SQL> SELECT * FROM EMPLOYEE WHERE DEPT_ID = (SELECT DEPT_ID
FROM DEPARTMENT WHERE DEPT_NAME = „ACCOUNTS‟);
COMMAND TO CHECK HOW MANY EMPLOYEES WORK FOR A/C DEPT
SQL> SELECT COUNT (*) FROM EMPLOYEE WHERE DEPT_ID =
(SELECT DEPT_ID FROM DEPARTMENT
WHERE DEPT_NAME = „ACCOUNTS‟);
COMMAND TO FIND MINIMUM, MAXIMUM AND AVERAGE SALARY OF
EMPLOYEES WORKING FOR ACCOUNTS DEPARTMENT
SQL > SELECT MIN (EMP_SALARY) FROM EMPLOYEE WHERE
DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENT WHERE
DEPT_NAME = „ACCOUNTS‟);
SQL > SELECT MAX (EMP_SALARY) FROM EMPLOYEE WHERE
DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENT WHERE
DEPT_NAME = „ACCOUNTS‟);
SQL > SELECT AVG (EMP_SALARY) FROM EMPLOYEE WHERE
DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENT WHERE
DEPT_NAME = „ACCOUNTS‟);
COMMAND TO LIST ALL EMPLOYEES WHO WORK FOR A PARTICULAR
SUPERVISOR
SQL > SELECT * FROM EMPLOYEE WHERE DEPT_ID =
(SELECT DEPT_ID FROM DEPARTMENT WHERE SUPERVISOR = „VIRAT‟);
COMMAND TO RE TRIEVE DEPARTMENT NAMES FOR EACH DEPARTMENT
WHERE ONLY ONE EMPLOYEE WORK
SQL> SELECT DEPT_NAME FROM DEPARTMENT WHERE
DEPT_ID IN (SELECT DEPT_ID FROM EMPLOYEE GROUP BY DEPT_ID
HAVING COUNT (*) = 1);
COMMAND TO INCREASE SALARY OF ALL EMPLOYEES IN THE SALES
DEPARTMENT BY 15%
SQL > UPDATE EMPLOYEE SET EMP_SALARY = EMP_SALARY +
EMP_ SALARY * 0.15 WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPARTMENT
WHERE DEPT_NAME = „SALES‟);
COMMAND TO ADD NEW COLUMN BONUS NUMBER (5) TO EMPLOYEE AND
COMPUTE 5% OF THE SALARY TO THE SAID FIELD
SQL > ALTER TABLE EMPLOYEE ADD BONUS NUMBER (5);
SQL > UPDATE EMPLOYEE SET BONUS = EMP_SALARY * 0.05;
COMMAND TO DELETE ALL THE EMPLOYEES IN THE MARKETING
DEPARTMENT
SQL > DELETE FROM EMPLOYEE WHERE DEPT_ID =
(SELECT DEPT_ID FROM DEPARTMENT WHERE
DEPT_NAME = „MARKETING‟);
COMMAND TO DISPLAY THE EMPLOYEE DATABASE

SQL > SELECT * FROM EMPLOYEE;

********************************
 CREATE A DATABASE FOR THE BANK TRANSACTION.
BANK TABLE
FIELD NAME FIELD TYPE DESCRIPTION
ACC_NO NUMBER(4) CUSTOMER A/C NO.
CUST_NAME VARCHAR2(20)CUSTOMER NAME
TRANS_AMT NUMBER(8,2) TRANSACTION AMOUNT
TRANS_DATE DATE TRANSACTION DATE
TRANS_TYPE CHAR(10) „D‟ FOR DEPOSIT
„W‟ FOR WITHDRAW
CUSTOMER TABLE
FIELD NAME FIELD TYPE
ACC_NO NUMBER(4)
CUST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
PHONE NUMBER(12)
1. INSERT DATA (10 RECORDS) INTO BANK AND CUSTOMER TABLE.
2. DISPLAY ALL RECORDS FROM BANK & CUSTOMER TABLE.
3. DISPLAY ALL RECORDS FROM BANK FOR A PARTICULAR DATE.
4. JOIN BANK AND CUSTOMER TABLE.
5. DISPLAY COUNT OF ALL RECORDS AND THEIR CORRESPONDING
TRANSACTION GROUP BY TRANSACTION TYPE.
6. DISPLAY ALL RECORDS FROM CUSTOMER ORDER BY CUSTOMER NAME
IN DESCENDING ORDER.
7. CHANGE THE TRANSA CTION AMOUNT.
8. ALTER THE TABLE CUSTOMER TO CHANGE THE SIZE OF CUSTOMER
ADDRESS.
9. DELETE RECORDS FROM BANK HAVING A PARTICULAR ACCOUNT
NUMBER.
10. CREATE A TABLE CONTAINING CUSTOMER ACCOUNT NUMBER RANGE.
11. DISPLAY ALL CUSTOMERS WHOSE NAME STARTS WITH „G‟
12. DISPLAY TODAY‟S DATE.
13. DISPLAY TOTAL TRANSACTION AMOUNT FROM BANK TABLE.
14. CREATE VIEW ON CUSTOMER SHOWING CUSTOMERS NUMBER AND
PHONE NUMBER.
15. DISPLAY DISTINCT CUSTOMER NUMBERS FROM BANK.
16. DISPLAY ACCOUNT NUMBER FROM BANK WHO HAVE MORE THAN ONE
TRANSACTION.
17. DISPLAY ALL RECORDS FROM CUSTOMER WHOSE PHONE NUMBER IS
NULL.
18. DELETE ALL RECORDS FROM CUSTOMER TABLE.
19. DROP BANK TABLE.
20. DROP CUSTOMER TABLE.
COMMAND TO CREATE CUSTOMER TABLE

SQL> CREATE TABLE CUSTOMER

(
ACC_NO NUMBER (4) PRIMARY KEY,
CUST_NAME VARCHAR2 (20),
ADDRESS VARCHAR2 (50),
PHONE NUMBER (12)
);

SQL > DESCRIBE CUSTOMER;

COMMAND TO CREATE BANK TABLE

SQL> CREATE TABLE BANK
(
ACC_NO NUMBER (4) NOT NULL,
CUST_NAME VARCHAR2 (20),
TRANS_AMT NUMBER (8, 2) CHECK (TRANS_AMT >0),
TRANS_DATE DATE,
TRANS_TYPE CHAR (10)
);
TO DISPLAY THE STRUCTURE
SQL> DESCRIBE BANK;
SQL > DESCRIBE CUSTOMER;

INSERT (10 RECORDS) DATA VALUES INTO BANK TABLE

SQL> INSERT INTO BANK VALUES (111,‟AYUSH‟, 5000,‟10-NOV-2017‟,‟DEPOSIT‟);
SQL> INSERT INTO BANK VALUES
(112‟,‟SACHIN‟, 300,‟11-NOV-2017‟,‟WITHDRAW‟);
INSERT (10 RECORDS) DATA INTO CUSTOMER TABLE
SQL > INSERT INTO CUSTOMER VALUES
(111,‟AYUSH‟,‟BANGALORE‟, „9945758629‟);

DISPLAY ALL RECORDS FROM BANK & CUSTOMER TABLE:

S QL > SELECT * FROM BANK;
SQL > SELECT * FROM CUSTOMER;

DISPLAY ALL RECORDS FROM BANK FOR A PARTICULAR DATE:

SQL > SELECT * FROM BANK WHERE TRANS_DATE = ‟13-NOV-2017‟;

JOIN BANK AND CUSTOMER TABLE

SQL > SELECT * FROM BANK, CUSTOMER
WHERE CUSTOMER.ACC_NO = BANK.ACC_NO;

DISPLAY COUNT OF ALL RECORDS AND THEIR CORRESPONDING

TRANSACTION GROUP BY TRANSACTION TYPE:
SQL > SELECT TRANS_TYPE, COUNT (*) FROM BANK GROUP BY TRANS_TYPE;

DISPLAY ALL RECORDS FROM CUSTOMER ORDER BY CUSTOMER NAME IN

DESCENDING ORDER:
SQL > SELECT * FROM CUSTOMER ORDER BY NAME DESC;

CHANGE THE TRANSACTION AMOUNT

SQL > UPDATE BANK SET TRANS_AMT = 15000 WHERE ACC_NO = 111;
SQL > SELECT ACC_NO, TRANS_AMT, TRANS_DATE, TRANS_TYPE FROM BANK;
ALTER THE TABLE TO CHANGE THE SIZE OF CUSTOMER ADDRESS
SQL> ALTER TABLE CUSTOMER MODIFY ADDRESS CHAR (25);
SQL > DESCRIBE CUSTOMER;

DELETE RECORDS FROM BANK WHERE ACC_NO = 114:

SQL > DELETE FROM BANK WHERE ACC_NO = 114;
SQL > SELECT * FROM BANK;

CREATE A TABLE CONTAINING CUSTOMER ACCOUNT RANGE:

SQL > CREATE TABLE TEMP_CUST AS (SELECT * FROM CUSTOMER
WHERE ACC_NO BETWEEN 113 AND 115);
SQL > SELECT * FROM TEMP-CUST;

DISPLAY ALL CUSTOMERS WHOSE NAME STARTS WITH ‘G’;

SQL> SELECT * FROM CUSTOMER WHERE CUST_NAME LIKE „G%‟;

DISPLAY TODAY’S DATE:

SQL> SELECT SYSDATE FROM DUAL;

DISPLAY TOTAL TRANSACTION AMOUNT FROM BANK TABLE:

SQL > SELECT SUM (TRANS-AMT) FROM BANK;

CREATE VIEW ON CUSTOMER SHOWING CUSTOMERS NUMBER AND

PHONE NUMBER:
SQL > CREATE VIEW CUST-DETAILS AS SELECT ACC_NO, PHONE
FROM CUSTOMER;
SQL > SELECT * FROM CUST_DETAILS;

DISPLAY DISTINCT CUSTOMER NUMBERS FROM BANK:

SQL> SELECT DISTINCT ACC_NO FROM BANK;
DISPLAY ACCOUNT NUMBER FROM BANK WHO HAVE MORE THAN
ONE TRANSACTION:
SQL > SELECT ACC_NO, COUNT (*) FROM BANK GROUP BY
ACC_NO HAVING COUNT (*) > 1;

DISPLAY ALL RECORDS FROM CUSTOMER WHOSE PHONE NUMBER IS

NULL:
SQL> SELECT * FROM CUSTOMER WHERE PHONE = „NULL‟;

DELETE ALL RECORDS FROM CUSTOMER TABLE:

SQL> DELETE FROM CUSTOMER;
SQL > SELECT * FROM CUSTOMER; (OUTPUT EMPTY SET)

DROP BANK TABLE:

SQL > DROP TABLE BANK;
SQL > SHOW TABLES;

DROP CUSTOMER TABLE:

SQL> DROP TABLE CUSTOMER;
SQL > SHOW TABLES;

******************************