Scribd Logo
Upload

Welcome to Scribd!

  • 18 views

    Wa0011

    Uploaded by

    krisshivram80

    Copyright:

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

    Wa0011

    Uploaded by

    krisshivram80
    18 views5 pages

    Original Title

    DOC-20241008-WA0011 (2)

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    18 views5 pages

    Wa0011

    Uploaded by

    krisshivram80

    Copyright:

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

    ex.

    no18
    SQL Table-1
    Aim: To create a table for students and execute the given commands using
    SQL.
    SQL Queries:
    A) To create a table Student with fields Studentid ,Studentname ,M1,M2,M3
    and Set Studentid as a Primary key
    create table Student(Studentid int primary key,
    studentname varchar(10), M1 int, M2 int, M3 int);

    B )To describe command is used to display the data definition, data types with
    default values
    Describe student;

    C) To insert values into Student table for the fields


    insert into student values(1100,'John',45,75,85);
    insert into student values(1102,'smith',55,67,87);

    D) To display the entire table student


    select * from student;

    E) To display the details of the student whose marks are greater than equal to 50
    Select * from student where m1>=50 and m2>=50 and m3>=50;
    ex.no19
    SQL table -2
    Aim: To create a table Consumer and execute the queries.
    A) To create a table Consumer with fields Consumerid , Consumer name
    ,Gender,Pincode and Set Consumerid as a Primary key
    create table consumer(consumerid int primary key,consumername
    varchar(30),Gender char(1), pincode int);
    B) Insert values into table
    insert into Consumer values(1200,'Grace','F',100012);
    insert into Consumer values(1201,'Nick','M',610012);
    insert into Consumer values(1204,'Berry','M',420012);
    insert into Consumer values(1207,'wood','M',210011);
    insert into Consumer values(1209,'Helen','F',410011);
    insert into Consumer values(1210,'Jennifer','F',510011);
    insert into Consumer values(1211,'Jennifer','F',510011);

    C) To display the consumer name from Table Consumer without redundancy


    select distinct consumername from consumer;

    D)To display the consumer name from Table consumer with redundancy
    select all consumername from consumer;

    E ) To Display the details of the consumer whose pincode ends with 12


    select * from consumer where pincode like '%12';

    F) To display the Department where the number of employees is less than or


    equal to 3
    Select dept,count(*) from employee group by dept having count(*)<=3;
    ex.no 20
    SQL TABLE-3
    Aim: To create a table for the Employee and execute the SQL queries
    SQL QUERIES
    create table employee(EmpId int primary key, Empname varchar(10), Dept
    char(8), salary int);

    insert into employee values(3100,'Smith','Finance',10000);


    insert into employee values(3102,'Robert','Sales',9000);
    insert into employee values(3104,'Martin','IT',11000);
    insert into employee values(3105,'Mathew','Shipping',7000);
    insert into employee values(3107,'Rose','Sales',10000);
    insert into employee values(3108,'Miller','IT',10000);
    insert into employee values(3109,'Adamford','Shipping',10000);

    A) To display the details of employee whose dept is IT and the salary is greater
    than or equal to 10000
    select * from employee where dept='IT' and salary>=10000;
    B) Display the Employee name in the Alphabetical order
    select * from employee order by empname;
    C) Display salary from greater to smaller.
    select * from employee order by salary desc;
    D) Display the column name empname as Employee name
    select empname as Employeename from employee;
    E) To increase salary of the Employee by 1000 where department name is
    Shipping
    select empid,empname,salary+1000 from employee where dept='Shipping';
    F) Enter a NULL value in the Department name of the Employee Table
    insert into employee values(3111,'George','NULL',9000)
    ex.no 21
    SQL TABLE-4
    Aim: To Execute the Aggregate functions using the Employee table
    SQL Queries:
    select * from employee;

    a) To update the employee name from null value.


    update employee set empname='Fathi' where empname is null;

    b) To calculate the Total salary of the Employee Department wise


    select Dept,sum(salary) from employee group by dept;

    c) To find the minimum salary employee from employee table


    select empid,empname,dept, min(salary) from employee ;

    d) ) To find the Maximum salary employee from employee table

    e) To calculate the average of the Employees Department wise


    select dept,avg(salary) from employee group by dept;

    f) To count the number of Employees


    Select count(dept) from employee;

    g) To count the number of Employees including Null values.


    Select count(*) from employee;

    h) To display the department wise count whose count is greater than 3 in each
    group
    select dept,count(*) from employee group by dept having count(*)<3;
    ex.no22
    SQL TABLE-5
    AIM
    To create table Stationary ,Stock,Sto and execute the commands.
    1)create table stationary(Item_no int primary key,
    -> Item varchar(20), Dcode int,Qty int, Unitprice int,StockDate Date);

    insert into stationary values(5005,"Ballpen",102,100,16,20220331);


    insert into stationary values(5002,"Gelpenclassic",101,200,22,20220912);
    insert into stationary values(5006,"Erasersmall",103,210,5,20221202);
    insert into stationary values(5008,"Eraserbig",103,60,10,20221101);
    insert into stationary values(5010,"Ballpen0.25",102,100,16,20220711);

    Create table Stock(Scode int primary key,Sname int);


    Insert into stock values(101,”Ballpen”)
    Insert into stock values(102,”Gelpen”)
    Insert into stock values(103,”Eraser”)

    Create table sto(Item_no int, dcode varchar(20),shopname varchar(10),Scode


    int);

    a)To add a foreign key in the table Stationary the parent table is Stock
    alter table stationary add foreign key (dcode) references stock(scode);
    b) To create a Cartesian product of the table Stationary and Stock
    Select * from stationary, stock;
    c) To display the details of the Stationary and Stock using EQUI join
    Select * from stationary, stock where stationary.dcode=stock.scode;

    d) To display the details of the Stock and Sto using Natural join
    select * from stock natural join sto;

    You might also like