Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    Dhruv Pillai IP Project#

    Uploaded by

    Dhruv Pillai

    Copyright:

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

    Dhruv Pillai IP Project#

    Uploaded by

    Dhruv Pillai
    5 views10 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    Dhruv Pillai IP Project#

    Uploaded by

    Dhruv Pillai

    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 10

    IP PROJECT

    Submitted by Dhruv Pillai

    Class XI G

    SET-1

    Instructions:

    Write only on the Right hand side of the page.

    Order of writing

    Question

    SQL query

    Output: Draw table for select query output (draw table using pencil)

    Write SQL queries to

    1. To create a database

    MYSQL> CREATE DATABASE SAMPLE;

    2. To create student table with the student id, class, section, gender, name, dob, and marks as attributes
    where the student id is the primary key.

    MYSQL> CREATE TABLE STUDENT(

    SID INT PRIMARY KEY,

    CLASS INT,

    SECTION CHAR(1),

    GENDER CHAR(1),

    NAME VARCHAR(25) NOT NULL,

    DOB DATE,

    MARKS FLOAT);

    3. Insert the details of at least 10 students in the above table

    MYSQL> INSERT INTO STUDENT VALUES(501, 11, 'C', 'M', 'ISAAM', '2005-01-20', 80);

    MYSQL> INSERT INTO STUDENT VALUES(602, 10, 'A', 'F', 'FIZZA', '2006-05-10', 85);

    MYSQL> INSERT INTO STUDENT VALUES(603, 10, 'B', 'F', 'ISHA', '2006-07-15', 80);

    MYSQL> INSERT INTO STUDENT VALUES(505, 11, 'D', 'M', 'RAZIL', '2005-10-25', 60);

    MYSQL> INSERT INTO STUDENT VALUES(506, 12, 'E', 'M', 'ALEX', '2005-03-18', 45);

    MYSQL> INSERT INTO STUDENT VALUES(507, 12, 'A', 'M', 'SAM', '2005-12-12', 50);
    MYSQL> INSERT INTO STUDENT VALUES(607, 10, 'B', 'F', 'MARIA', '2006-02-15', 70);

    MYSQL> INSERT INTO STUDENT VALUES(608, 10, 'E', 'F', 'TANISHA', '2006-04-11', 65);

    MYSQL> INSERT INTO STUDENT VALUES(509, 11, 'B', 'M', 'AKHIL', '2005-03-01', 40);

    MYSQL> INSERT INTO STUDENT VALUES(510, 11, 'B', 'M', 'ARIL', '2005-06-18', 90);

    4. To display the entire content of table.

    MYSQL> SELECT * FROM STUDENT;

    Output:

    5. To display Student id, Name and Marks of those students who are scoring marks more than 50.

    MYSQL> SELECT SID, NAME, MARKS FROM STUDENT WHERE MARKS>50;

    Output:
    8. To display the information all the students, whose name starts with ‘AK’

    MYSQL> SELECT * FROM STUDENT WHERE NAME LIKE 'AK%';

    Output:

    9. To display Sid, Name, DOB of those students who are born between ‘2005- 01-01’ and ‘2005-12-31’.

    MYSQL> SELECT SID, NAME, DOB FROM STUDENT WHERE DOB BETWEEN '2005-01-01' AND '2005-12-
    31';

    Output:

    10. To display Student ID, Name, DOB and Marks of male students in ascending order of their names.

    MYSQL> SELECT SID, NAME, DOB, MARKS FROM STUDENT WHERE GENDER LIKE 'M' ORDER BY NAME;

    Output:

    11. To display Student ID, Gender, Name, DOB, Marks in descending order of their marks.

    MYSQL> SELECT SID, GENDER, NAME, DOB, MARKS FROM STUDENT ORDER BY MARKS DESC;

    Output:
    12. To display the unique section available in the table.

    MYSQL> SELECT DISTINCT SECTION FROM STUDENT;

    Output:

    Class XI SQL – QUERIES TO WRITE IN PRACTICAL RECORD BOOK

    SET-2

    Write SQL queries for the following questions

    CREATE TABLE TEACHER WITH FOLLOWING SPECIFICATIONS

    TABLE NAME: TEACHER


    FIELD DATA TYPE
    TID INT (to store teacher id, set as primary key)
    TNAME VARCHAR(20) to store teacher name, column value cannot be blank
    DEPT VARCHAR(20) to store the department of teacher
    DOJ DATE to store date of joining
    SALARY DOUBLE(10,2) to store salary of teachers

    MYSQL>CREATE TABLE TEACHER (


    TID INT PRIMARY KEY,
    TNAME VARCHAR(20) NOT NULL,
    DEPT VARCHAR(20),
    DOJ DATE,
    SALARY DOUBLE(10,2)
    );

    Insert the following records to the above table

    TID TNAME DEPT DOJ SALARY


    101 SAM MATHS 11-Mar-01 4500
    102 SARAH COMPUTER 15-Jan-02 5000
    103 MANILA SCIENCE 20-Dec-05 3750
    104 ROY MATHS 15-Jun-06 3250
    105 MARY COMPUTER 18-Apr-03 4000
    106 JOHN NULL 30-Aug-07 3600

    MYSQL> INSERT INTO TEACHER VALUES(101,’SAM’,’MATHS’,’2001-03-11’,4500);

    Write the queries to insert remaining records

    1. Display the teacher names and the departments in which they are working
    MYSQL> SELECT TNAME, DEPT FROM TEACHER;
    Output:

    2. Display the teacher names and their joining dates in school


    MYSQL> SELECT TNAME, DOJ FROM TEACHER;
    Output:

    3. Display the name of the teacher with the teacher id 104


    MYSQL>SELECT TNAME FROM TEACHER WHERE TID=104;
    Output:
    4. Display the name of teachers working in COMPUTER department.
    MYSQL> SELECT TNAME , DEPT FROM TEACHER WHERE DEPT LIKE “COMPUTER”;
    Output:

    5. Display the names of teachers having salary above 3500


    MYSQL> SELECT TNAME , SALARY FROM TEACHER WHERE SALARY>3500;
    Output:

    6. Display the names of teachers having salary above 4000 working in MATHS department
    MYSQL> SELECT TNAME, SALARY, DEPT FROM TEACHER WHERE SALARY>4000 AND DEPT LIKE
    “MATHS”;
    Output:

    7. Display the details of teachers drawing salary in the range of 3500 to 4500
    MYSQL>SELECT * FROM TEACHER WHERE SALARY BETWEEN 3500 AND 4500;
    Output:

    8. Display the various departments in school


    MYSQL>SELECT DISTINCT DEPT FROM TEACHER;
    Output:
    9. Display the teachers according to seniority (in the order of Joining dates)
    MYSQL> SELECT* FROM TEACHER ORDER BY DOJ;
    Output:

    10. Display teacher names in descending order


    MYSQL> SELECT TNAME FROM TEACHER ORDER BY TNAME DESC;
    Output:

    11. Sort and display teacher names first by department and then by teacher names
    MYSQL>SELECT TNAME, DEPT FROM TEACHER ORDER BY DEPT, TNAME;
    Output:
    12. Display teacher name and department names who are not working in MATHS and COMPUTER
    department
    MYSQL>SELECT TNAME, DEPT FROM TEACHER WHERE DEPT NOT IN ('MATHS', 'COMPUTER');
    Output:

    13. Display teachers who are working in SCIENCE and COMPUTER department
    MYSQL>SELECT TNAME, DEPT FROM TEACHER WHERE DEPT IN ('SCIENCE', 'COMPUTER');
    Output:

    14. Display names of teachers starting with letter S


    MYSQL> SELECT TNAME FROM TEACHER WHERE TNAME LIKE “S%”;
    Output:

    15. Display names of teachers ending with letter Y


    MYSQL> SELECT TNAME FROM TEACHER WHERE TNAME LIKE “%Y”;
    Output:
    16. Display names of teachers having third letter R in their name
    MYSQL> SELECT TNAME FROM TEACHER WHERE TNAME LIKE “__R%”;
    Output:

    17. Display names of teachers with exactly 3 letters


    MYSQL> SELECT TNAME FROM TEACHER WHERE TNAME LIKE “___”;
    Output:

    18. Add a new column bonus of type float

    MYSQL>ALTER TABLE TEACHER ADD BONUS FLOAT;

    19. Assign 10% bonus to those teacher who are getting salary below 4000

    MYSQL>UPDATE TEACHER SET BONUS=SALARY*10/100 WHERE SALARY<4000;

    20. Display the teachers who are getting bonus

    MYSQL>SELECT TNAME, BONUS FROM TEACHER WHERE BONUS IS NOT NULL;

    Output:
    21. Find the number of teachers getting salary above 4000
    MYSQL> select count(tname) from teacher where salary>4000;
    Output:

    22. Find the total salary paid to teachers


    MYSQL> select sum(salary) from teacher;
    Output:

    23. Find the average bonus paid to teachers


    MYSQL> select avg(bonus) from teacher;
    Output:

    24. Find the maximum and minimum salary paid to teachers and display with column headings
    Highest Salary and Lowest Salary

    MYSQL> select max(salary) as 'Highest Salary', min(salary) as 'Lowest Salary' from teacher;

    Output:

    25. Remove the column bonus from teacher table


    MYSQL> alter table teacher drop column bonus;
    26. Delete the records of teachers who are not getting bonus
    MYSQL>delete from teacher where bonus is null;

    You might also like