Scribd Logo
Upload

Welcome to Scribd!

  • 36 views

    Create: Create Table Emp354 (Emp - Id Varchar2 (10), Emp - Name Varchar2 (10), Department Varchar2 (10) )

    Uploaded by

    rishabh kumar
    The document discusses SQL commands for creating, altering, dropping, and populating database tables. It includes commands for DDL, DML, integrity constraints, aggregation with GROUP BY, and subqueries. Examples are provided for CREATE, ALTER, DROP, INSERT, SELECT, UPDATE, DELETE, CHECK constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, GROUP BY with aggregation functions, and correlated subqueries.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Create: Create Table Emp354 (Emp - Id Varchar2 (10), Emp - Name Varchar2 (10), Department Varchar2 (10) )

    Uploaded by

    rishabh kumar
    36 views9 pages
    The document discusses SQL commands for creating, altering, dropping, and populating database tables. It includes commands for DDL, DML, integrity constraints, aggregation with GROUP BY, and subqueries. Examples are provided for CREATE, ALTER, DROP, INSERT, SELECT, UPDATE, DELETE, CHECK constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, GROUP BY with aggregation functions, and correlated subqueries.

    Original Description:

    v gv nhu

    Original Title

    1392414

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses SQL commands for creating, altering, dropping, and populating database tables. It includes commands for DDL, DML, integrity constraints, aggregation with GROUP BY, and subqueries. Examples are provided for CREATE, ALTER, DROP, INSERT, SELECT, UPDATE, DELETE, CHECK constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, GROUP BY with aggregation functions, and correlated subqueries.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    36 views9 pages

    Create: Create Table Emp354 (Emp - Id Varchar2 (10), Emp - Name Varchar2 (10), Department Varchar2 (10) )

    Uploaded by

    rishabh kumar
    The document discusses SQL commands for creating, altering, dropping, and populating database tables. It includes commands for DDL, DML, integrity constraints, aggregation with GROUP BY, and subqueries. Examples are provided for CREATE, ALTER, DROP, INSERT, SELECT, UPDATE, DELETE, CHECK constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, GROUP BY with aggregation functions, and correlated subqueries.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 9

    1)DDL

    CREATE
    Create table emp354(emp_id varchar2(10),emp_name varchar2(10),department varchar2(10
    ));

    ALTER
    Alter table emp354 add (salary varchar2(10));

    DROP
    Drop table emp354;
    DML
    INSERT
    Insert into emp354 values (‘1001’,’noni’,’accounts’,’44000’);
    Insert into emp354 values (‘1002’,’riya’,’sales’,’40000’);
    Insert into emp354 values (‘1003’,’priya’,’production’,’50000’);
    Insert into emp354 values (‘1004’,’karan’,’sales’,’40000’);
    Insert into emp354 values (‘1005’,’akki’,’accounts’,’30000’);
    Insert into emp354 values (‘1006’,’ram’,’finance’,’40000’);

    SELECT
    Select * from emp354 where emp_id=’1005’;

    UPDATE
    Update emp354 set salary=salary+1000;
    DELETE
    Delete from emp354 where emp_id=’1001’;

    2)CHECK CONSTRAINTS
    a) Create table product_354(product_id varchar2(10) check (product_id like ‘p%’),pname
    varchar2(10),qty number(5),mrp number(10) check (mrp>0));

    b) create table supplier_354(supplier_id varchar2(10) primary key, supplier_name varchar2(10),city


    varchar(15) check(city in('delhi','jaipur','ajmer','mumbai')));

    insert into supplier_354 values('1001','nandini','delhi');


    insert into supplier_354 values('1002','sohan','jaipur');
    insert into supplier_354 values('1003','aniket','mumbai');
    insert into supplier_354 values('1004','mohan','delhi');
    insert into supplier_354 values('1005','riya','ajmer');
    3)SIMPLE QUERY (USE OF ‘AND’,’OR’)
    AND
    Select * from emp354 where department=’accounts’ and salary<42000;

    OR
    Select * from emp354 where department=’accounts’ or salary<42000;
    4)INTEGRITY CONSTRAINTS
    create table supplier_354(supplier_id varchar2(10) primary key, supplier_name varchar2(15), city
    varchar(15));
    insert into supplier_354 values('S001','nandini','delhi');
    insert into supplier_354 values('S002','mohan','kolkata');
    insert into supplier_354 values('S003','aniket','mumbai');
    insert into supplier_354 values('S004','ram','delhi');
    insert into supplier_354 values('S005','anil','mumbai');

    create table product_354(product_id varchar2(10) primary key, product_name varchar2(10), qty


    number(6),supplier_id varchar2(10) references supplier_354(supplier_id));
    insert into product_354 values('P001','monitor',15,'S001');
    insert into product_354 values('P002','keyboard',15,'S002');
    insert into product_354 values('P003','CPU',10,'S003');
    insert into product_354 values('P004','printer',20,'S004');
    insert into product_354 values('P005','speaker',12,'S005');

    5)Group by
    a) select count(department), salaryfrom emp354group by salary;

    b) select count(department), salaryfrom emp354group by salaryorder by count(department) desc;


    c) select department, sum(salary) from emp354 group by department;

    d) select department, min(salary) from emp354 group by department;

    e) select department, max(salary) from emp354 group by department;

    f) select department, avg(salary) from emp354 group by department;

    6) Sub query
    Select product_name from product_354 where supplier_id=’S001’;

    Select sum(qty) from product_354 group by supplier_id having supplier_id=’S003’;

    Select product_name from product_354 where product_id=’P004’;

    Select * from product_354 where qty>10;

    Select product_name from product_354 where supplier_id=’S001’ or supplier_id=’S002’;


    Select city from supplier_354 where supplier_name=’nandini’;

    Select Product_name from product_354 where supplier_id=(select supplier_id from supplier_354 where
    supplier_name =’nandini’);

    Select supplier_name from supplier_354 where supplier_id=(select supplier_id from product_354 where
    product_name=’monitor’);

    Select supplier_name from supplier_354 where supplier_id=( select supplier_id from product_354
    where qty=’20’);

    Select supplier_id from supplier_354 where supplier_name=’mohan’;

    You might also like