Scribd Logo
Upload

Welcome to Scribd!

  • 86 views

    Expeiment 3: Queries in SQL: Program To Create Database

    Uploaded by

    Rohit Aggarwal
    The document discusses experiments with SQL queries to create, populate, modify, and view tables in a database. It includes examples of creating tables with different constraints, inserting records, accepting user input, selecting records with various conditions, updating, deleting, altering tables, ordering records, and using aggregate functions.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd

    Expeiment 3: Queries in SQL: Program To Create Database

    Uploaded by

    Rohit Aggarwal
    86 views18 pages
    The document discusses experiments with SQL queries to create, populate, modify, and view tables in a database. It includes examples of creating tables with different constraints, inserting records, accepting user input, selecting records with various conditions, updating, deleting, altering tables, ordering records, and using aggregate functions.

    Original Title

    EXPEIMENT 3

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses experiments with SQL queries to create, populate, modify, and view tables in a database. It includes examples of creating tables with different constraints, inserting records, accepting user input, selecting records with various conditions, updating, deleting, altering tables, ordering records, and using aggregate functions.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    86 views18 pages

    Expeiment 3: Queries in SQL: Program To Create Database

    Uploaded by

    Rohit Aggarwal
    The document discusses experiments with SQL queries to create, populate, modify, and view tables in a database. It includes examples of creating tables with different constraints, inserting records, accepting user input, selecting records with various conditions, updating, deleting, altering tables, ordering records, and using aggregate functions.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 18

    PRACTICAL FILE OF DBMS

    EXPEIMENT 3: QUERIES IN SQL


    PROGRAM TO CREATE DATABASE
    CREATION OF TABLE:
    SQL> CREATE TABLE EMPLOYEE(EMP_NONUMBER(2), 2 NAME VARCHAR(10), 3 ADDRESS VARCHAR(10)); TABLE CREATED. SQL> CREATE TABLE PAT(ROOM_NO NUMBER(2), 2 NAME VARCHAR(10), 3 DISEASE VARCHAR(15)); TABLE CREATED. DESCRIBING THE TABLE: SQL> DESC EMPLOYEE; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------EMP_NO NAME ADDRESS NUMBER(2) VARCHAR2(10) VARCHAR2(10)

    SQL> DESC PAT; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------ROOM_NO NAME DISEASE NUMBER(2) VARCHAR2(10) VARCHAR2(15)

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    CREATE TABLE WITH CONSTRAINTS


    PRIMARY KEY CONSTRAINT:
    SQL> CREATE TABLE EMPLOYEE2(EMPAT_ID NUMBER(3) PRIMARY KEY, 2 NAME VARCHAR(10), 3 PHONE NUMBER(10), 8 ADDRESS VARCHAR(20)); TABLE CREATED. NOT NULL CONSTRAINT: SQL> CREATE TABLE EMPLOYEE3(EMPAT_ID NUMBER(3) NOT NULL, 2 NAME VARCHAR(10), 3 PHONE NUMBER(10), 8 ADDRESS VARCHAR(20)); TABLE CREATED. UNIQUE CONSTRAINT: SQL> CREATE TABLE EMPLOYEE8(EMPAT_ID NUMBER(3) UNIQUE, 2 NAME VARCHAR(10), 3 PHONE NUMBER(10), 8 ADDRESS VARCHAR(20)); TABLE CREATED. CHECK CONSTRAINT: SQL> CREATE TABLE EMPLOYEE5(E_ID NUMBER(3), 2 FNAME VARCHAR(10) CHECK(FNAME=UPPER(FNAME)), 3 MNAME VARCHAR(10) CHECK(MNAME=UPPER(MNAME)), 8 LNAME VARCHAR(10) CHECK(LNAME=UPPER(LNAME)), 5 DESIGNATION VARCHAR(10)); TABLE CREATED. FOREIGN KEY CONSTRAINT: SQL> CREATE TABLE EMP6(E_ID NUMBER(3) REFERENCES EMPLOYEE2(EMPAT_ID) ON DELETE CASCADE, 2 PHONE NUMBER(10), 3 ADDRESS VARCHAR(20), 8 NAME VARCHAR(10)); TABLE CREATED.

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    ADD A RECORD TO THE DATABASE:


    SIMPLE INSERT COMMAND:
    SQL> INSERT INTO EMPLOYEE VALUES(16,'NURUL','NARAINA'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(81,'DEEPAK','NAGPUR'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(79,'SHIVAM','ADARSH NAGAR'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(78,'SHUBHAM','DELHI'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(98,'SAKSHI','DELHI'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(88,'ROHIT','DELHI'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(87,'RITU','DELHI'); 1 ROW CREATED. SQL> INSERT INTO EMPLOYEE VALUES(92,'SAHIL','DELHI'); 1 ROW CREATED.

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    ACCEPTING VALUES FROM THE USER:


    SQL> INSERT INTO EMPLOYEE VALUES(&EMP_NO,'&NAME','&ADDRESS'); ENTER VALUE FOR EMP_NO: 71 ENTER VALUE FOR NAME: POOJA ENTER VALUE FOR ADDRESS: NAGPUR OLD 1: INSERT INTO EMPLOYEE VALUES(&EMP_NO,'&NAME','&ADDRESS') NEW 1: INSERT INTO EMPLOYEE VALUES(71,'POOJA','NAGPUR') 1 ROW CREATED. SQL> / ENTER VALUE FOR EMP_NO: 50 ENTER VALUE FOR NAME: HIMANSHU ENTER VALUE FOR ADDRESS: U.P. OLD 1: INSERT INTO EMPLOYEE VALUES(&EMP_NO,'&NAME','&ADDRESS') NEW 1: INSERT INTO EMPLOYEE VALUES(50,'HIMANSHU','U.P.') 1 ROW CREATED. SQL> / ENTER VALUE FOR EMP_NO: 10 ENTER VALUE FOR NAME: TANYA ENTER VALUE FOR ADDRESS: NAGPUR OLD 1: INSERT INTO EMPLOYEE VALUES(&EMP_NO,'&NAME','&ADDRESS') NEW 1: INSERT INTO EMPLOYEE VALUES(10,'TANYA','NAGPUR') 1 ROW CREATED. SQL> / ENTER VALUE FOR EMP_NO: 32 ENTER VALUE FOR NAME: VANSHIKA ENTER VALUE FOR ADDRESS: NAGPUR OLD 1: INSERT INTO EMPLOYEE VALUES(&EMP_NO,'&NAME','&ADDRESS') NEW 1: INSERT INTO EMPLOYEE VALUES(32,'VANSHIKA','NAGPUR') 1 ROW CREATED.

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    VIEWING THE DATABASE:


    SELECT ALL:
    SQL> SELECT * FROM EMPLOYEE; EMP_NONAME ADDRESS ---------- ---------- ---------16 NURUL NARAINA 81 DEEPAK NAGPUR 79 SHIVAM ADARSH NAGAR 78 SHUBHAM DELHI 98 SAKSHI DELHI 88 ROHIT DELHI 87 RITU DELHI 92 SAHIL DELHI 71 POOJA NAGPUR 50 HIMANSHU U.P. 10 TANYA NAGPUR EMP_NONAME ADDRESS ---------- ---------- ---------32 VANSHIKA NAGPUR 12 ROWS SELECTED. SELECTED COLUMNS IN ALL ROWS: SQL> SELECT EMP_NOFROM EMPLOYEE; EMP_NO ---------16 81 79 78 98 88 87 92 71 50 10 ROHIT AGGARWAL 41088

    PRACTICAL FILE OF DBMS

    EMP_NO ---------32 12 ROWS SELECTED. SELECTED ROWS IN ALL COLUMNS: SQL> SELECT * FROM EMPLOYEE WHERE EMP_NO=16; EMP_NONAME ADDRESS ---------- ---------- ---------16 NURUL NARAINA SELECTED ROWS AND SELECTED COLUMNS: SQL> SELECT NAME,ADDRESS FROM EMPLOYEE WHERE EMP_NO=79; NAME ADDRESS ---------- ---------SHIVAM ADARSH NAGAR

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    DELETING THE DATABASE:


    DELETE SINGLE RECORD:
    SQL> DELETE FROM EMPLOYEE WHERE EMP_NO=50; 1 ROW DELETED. SQL> DESC EMPLOYEE; SQL> SELECT * FROM EMPLOYEE; EMP_NONAME ADDRESS ---------- ---------- ---------16 NURUL NARAINA 81 DEEPAK NAGPUR 79 SHIVAM ADARSH NAGAR 78 SHUBHAM DELHI 98 SAKSHI DELHI 88 ROHIT DELHI 87 RITU DELHI 92 SAHIL DELHI 71 POOJA NAGPUR 10 TANYA NAGPUR 32 VANSHIKA NAGPUR 11 ROWS SELECTED. DELETING MULTIPLE RECORD: SQL> DELETE FROM EMPLOYEE WHERE ADDRESS='NAGPUR'; 8 ROWS DELETED. SQL> SELECT * FROM EMPLOYEE; EMP_NONAME ADDRESS ---------- ---------- ---------16 NURUL NARAINA 79 SHIVAM ADARSH NAGAR 78 SHUBHAM DELHI 98 SAKSHI DELHI 88 ROHIT DELHI 87 RITU DELHI 92 SAHIL DELHI 7 ROWS SELECTED. ROHIT AGGARWAL 41088

    PRACTICAL FILE OF DBMS

    DELETE ALL RECORDS: SQL> DELETE FROM EMPLOYEE; 7 ROWS DELETED. SQL> SELECT * FROM EMPLOYEE; NO ROWS SELECTED TRUNCATE TABLE: SQL> TRUNCATE TABLE EMPLOYEE; TABLE TRUNCATED. SQL> SELECT * FROM EMPLOYEE; NO ROWS SELECTED DROPPING THE TABLE: SQL> DROP TABLE EMPLOYEE; TABLE DROPPED.

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    MODIFYING THE RECORD:


    WITH WHERE CLAUSE:
    SQL> UPDATE PAT SET NAME ='HIMANSHU' WHERE NAME='PRIYA'; 1 ROW UPDATED. SQL> SELECT * FROM PAT; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 SURBHI DELHI 32 HIMANSHU NAGPUR 96 SOURABH DELHI 50 KANIKA NAGPUR SQL> UPDATE PAT SET NAME ='HIMANSHU'; 8 ROWS UPDATED. WITHOUT WHERE CLAUSE: SQL> SELECT * FROM PAT; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 HIMANSHU DELHI 32 HIMANSHU NAGPUR 96 HIMANSHU DELHI 50 HIMANSHU NAGPUR

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    ALTER THE TABLE:


    ADDING COLUMN:
    SQL> ALTER TABLE EMP ADD(DEPT VARCHAR(10)); TABLE ALTERED. SQL> SELECT * FROM EMP; EMPAT_ID NAME PHONE ADDRESS ---------- ---------- ---------- -------------------- ---------32 SATISH 78673865 RAJOURI GARDEN 38 RAMESH 87638979 ROHINI 98 SURESH 96778876 MAYAPURI 76 SIDDHU 98326766 JANAKPURI 53 RIHESH 87799555 PITAMPURA DEPT

    CHANGING COLUMN WIDTH: SQL> ALTER TABLE EMP MODIFY(NAME VARCHAR(6)); TABLE ALTERED. SQL> DESC EMP; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------EMPAT_ID NAME PHONE ADDRESS DEPT NUMBER(3) VARCHAR2(6) NUMBER(10) VARCHAR2(20) VARCHAR2(10)

    ADDING MULTIPLE COLUMNS: SQL> ALTER TABLE EMP ADD(VOT_ID NUMBER(2), GENDER VARCHAR(6)); TABLE ALTERED. SQL> SELECT * FROM EMP; EMPAT_ID NAME PHONE ADDRESS DEPT ---------- ------ ---------- -------------------- ---------- ---------- -----32 SATISH 78673865 RAJOURI GARDEN 38 RAMESH 87638979 ROHINI 98 SURESH 96778876 MAYAPURI ROHIT AGGARWAL VOT_ID GENDER

    41088

    PRACTICAL FILE OF DBMS 76 SIDDHU 98326766 JANAKPURI 53 RIHESH 87799555 PITAMPURA DROPPING COLUMN: SQL> ALTER TABLE EMP DROP COLUMN GENDER; TABLE ALTERED. SQL> DESC EMP; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------EMPAT_ID NAME PHONE ADDRESS DEPT VOT_ID NUMBER(3) VARCHAR2(6) NUMBER(10) VARCHAR2(20) VARCHAR2(10) NUMBER(2)

    ADDING PRIMARY KEY: SQL> ALTER TABLE PAT ADD PRIMARY KEY(ROOM_NO); TABLE ALTERED. SQL> DESC PAT; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------ROOM_NO NAME DISEASE PAT_ID DAY NOT NULL NUMBER(2) VARCHAR2(15) VARCHAR2(15) NUMBER(3) VARCHAR2(10)

    REMOVING PRIMARY KEY: SQL> ALTER TABLE PAT DROP PRIMARY KEY; TABLE ALTERED. SQL> DESC PAT; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------ROOM_NO NAME DISEASE NUMBER(2) VARCHAR2(15) VARCHAR2(15)

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS PAT_ID DAY NUMBER(3) VARCHAR2(10)

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    ORDERING THE RECORD:


    ASCENDING ORDER:
    SQL> SELECT * FROM PAT ORDER BY ROOM_NO ASC; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------50 KANIKA NAGPUR 96 SOURABH DELHI 32 SURBHI DELHI 32 PRIYA NAGPUR DESCENDING ORDER: SQL> SELECT * FROM PAT ORDER BY ROOM_NO DESC; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 PRIYA NAGPUR 32 SURBHI DELHI 96 SOURABH DELHI 50 KANIKA NAGPUR

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    GROUPING THE RECORDS:


    WITHOUT HAVING CLAUSE:
    SQL> SELECT NAME FROM PAT GROUP BY NAME; NAME --------------SURBHI SOURABH PRIYA KANIKA WITH HAVING CLAUSE: SQL> SELECT ROOM_NO FROM PAT GROUP BY ROOM_NO HAVING ROOM_NO=96; ROOM_NO ---------96

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    ORACLE FUNCTION:
    SUM:
    SQL> SELECT SUM(ROOM_NO) FROM PAT; SUM(ROOM_NO) -----------502

    COUNT:
    SQL> SELECT COUNT(ROOM_NO) FROM PAT; COUNT(ROOM_NO) -------------8

    COUNT DISTINCT:
    SQL> SELECT COUNT(ROOM_NO) FROM PAT WHERE NAME='PRIYA'; COUNT(ROOM_NO) -------------1

    AVERAGE:
    SQL> SELECT AVG(ROOM_NO) FROM PAT; AVG(ROOM_NO) -----------36.96

    MAXIMUM:
    SQL> SELECT MAX(ROOM_NO) FROM PAT; MAX(ROOM_NO) -----------32

    MINIMUM:
    SQL> SELECT MIN(ROOM_NO) FROM PAT; ROHIT AGGARWAL 41088

    PRACTICAL FILE OF DBMS

    MIN(ROOM_NO) -----------50

    GENERATING SUB QUERIES:


    SQL> SELECT * FROM PAT WHERE NAME LIKE'P___A%'; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 PRIYA NAGPUR SQL> SELECT * FROM PAT WHERE NAME='SURBHI' OR ROOM_NO=96 ; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 SURBHI DELHI 96 SOURABH DELHI SQL> SELECT * FROM PAT WHERE NAME='SOURABH' AND ROOM_NO=96 ; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------96 SOURABH DELHI SQL> SELECT * FROM PAT WHERE NAME!='SOURABH'; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 SURBHI DELHI 32 PRIYA NAGPUR 50 KANIKA NAGPUR SQL> SELECT * FROM PAT WHERE ROOM_NO>96 ; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------32 SURBHI DELHI 32 PRIYA NAGPUR SQL> SELECT * FROM PAT WHERE ROOM_NO<96 ; ROOM_NO NAME DISEASE PAT_ID DAY ---------- --------------- --------------- ---------- ---------50 KANIKA NAGPUR

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS

    DUAL TABLE:
    SQL> DESC DUAL; NAME NULL? TYPE ----------------------------------------- -------- ---------------------------DUMMY SQL> SELECT 8*8 FROM DUAL; 8*8 ---------16 SQL> SELECT SYSDATE FROM DUAL; SYSDATE --------29-NOV-00 SQL> SELECT LAST_DAY('28-AUG-1990') FROM DUAL; LAST_DAY( --------31-AUG-90 SQL> SELECT MONTHS_BETWEEN ('28-JAN-2010','11-MAY-2009') "MONTHS"FROM DUAL; MONTHS ---------8.81935888 SQL> SELECT UPPER('NURUL') FROM DUAL; UPPER ----NURUL SQL> SELECT LOWER('NURUL') FROM DUAL; LOWER ----NURUL VARCHAR2(1)

    ROHIT AGGARWAL

    41088

    PRACTICAL FILE OF DBMS SQL> SELECT INITCAP('NURUL') FROM DUAL; INITC ----NURUL SQL> SELECT ASCII('V') FROM DUAL; ASCII('V') ---------118 SQL> SELECT LENGTH('NURUL') FROM DUAL; LENGTH('NURUL') --------------5

    SET OPERATIONS
    SQL> SELECT NAME FROM EMP UNION SELECT ADDRESS FROM EMP; NAME -------------------JANAKPURI MAYAPURI PITAMPURA RAJOURI GARDEN RAMESH RIHESH ROHINI SATISH SIDDHU SURESH 10 ROWS SELECTED. SQL> SELECT NAME FROM EMP MINUS SELECT NAME FROM EMP1; NAME --------GIRISH RAMESH SATISH SIDDU SURESH SQL> SELECT NAME FROM EMP INTERSECT SELECT NAME FROM EMP1; NAME ---------RAKESH

    ROHIT AGGARWAL

    41088

    You might also like