Scribd Logo
Upload

Welcome to Scribd!

  • 14 views

    Tables and Errors: Zawiyar Ahmad 181340

    Uploaded by

    Zawiyar Ahmad
    The document contains SQL statements to create tables for workers, bonuses, and titles along with sample data. It also includes various SELECT queries to retrieve and manipulate data from the tables including filtering, aggregation, sorting, and set operations.

    Copyright:

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

    Tables and Errors: Zawiyar Ahmad 181340

    Uploaded by

    Zawiyar Ahmad
    14 views7 pages
    The document contains SQL statements to create tables for workers, bonuses, and titles along with sample data. It also includes various SELECT queries to retrieve and manipulate data from the tables including filtering, aggregation, sorting, and set operations.

    Original Title

    181340 Q1

    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 contains SQL statements to create tables for workers, bonuses, and titles along with sample data. It also includes various SELECT queries to retrieve and manipulate data from the tables including filtering, aggregation, sorting, and set operations.

    Copyright:

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

    Tables and Errors: Zawiyar Ahmad 181340

    Uploaded by

    Zawiyar Ahmad
    The document contains SQL statements to create tables for workers, bonuses, and titles along with sample data. It also includes various SELECT queries to retrieve and manipulate data from the tables including filtering, aggregation, sorting, and set operations.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 7

    ZAWIYAR AHMAD

    181340

    TABLES AND ERRORS

    INSERT INTO Worker

    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Asim', 'Rana', 100000, '2014-02-20 09:00:00', 'HR')

    INSERT INTO Worker

    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Anum', 'Jahngir', 80000, '2014-06-11 09:00:00', 'Admin')

    INSERT INTO Worker


    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Vishal', 'Kiyani', 300000, '2014-02-20 09:00:00', 'HR')

    INSERT INTO Worker

    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Asra', 'Masood', 500000, '2014-02-20 09:00:00', 'Admin')

    INSERT INTO Worker

    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Hurriat', 'Ali', 500000, '2014-06-11 09:00:00', 'Admin')

    INSERT INTO Worker

    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Muhammd', 'Hashim', 200000, '2014-06-11 09:00:00', 'Account')

    INSERT INTO Worker

    ( FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

    ('Saood', 'Ahmad', 75000, '2014-01-20 09:00:00', 'Account'),

    ('Eyeshah', 'Shah', 90000, '2014-04-11 09:00:00', 'Admin');

    select *from Worker

    BONUS TABLE
    CREATE TABLE Bonus (
    WORKER_REF_ID INT Primary key identity(1,1),
    BONUS_AMOUNT INT,
    BONUS_DATE DATETIME,
    WORKER_ID INT FOREIGN KEY REFERENCES Worker (WORKER_ID)
    );
    INSERT INTO Bonus
    ( BONUS_AMOUNT, BONUS_DATE,WORKER_ID)
    VALUES( 5000, '2016-02-20',1)

    INSERT INTO Bonus


    ( BONUS_AMOUNT, BONUS_DATE,WORKER_ID)
    VALUES ( 3000, '2016-06-11',2)

    INSERT INTO Bonus


    ( BONUS_AMOUNT, BONUS_DATE,WORKER_ID)
    VALUES ( 4000, '2016-02-20',3)

    INSERT INTO Bonus


    ( BONUS_AMOUNT, BONUS_DATE,WORKER_ID)
    VALUES ( 4500, '2016-02-20',4)

    INSERT INTO Bonus


    ( BONUS_AMOUNT, BONUS_DATE,WORKER_ID)
    VALUES ( 3500, '2016-06-11',5)

    TABLE TITLE

    CREATE TABLE Title (


    WORKER_REF_ID INT,
    WORKER_TITLE CHAR(25),
    AFFECTED_FROM DATETIME,
    FOREIGN KEY (WORKER_REF_ID)
    REFERENCES Worker(WORKER_ID)
    );

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (001, 'Manager', '2016-02-20 00:00:00')

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (002, 'Executive', '2016-06-11 00:00:00')

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (008, 'Executive', '2016-06-11 00:00:00')

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (005, 'Manager', '2016-06-11 00:00:00')

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (004, 'Asst. Manager', '2016-06-11 00:00:00')
    INSERT INTO Title
    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (007, 'Executive', '2016-06-11 00:00:00')

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (006, 'Lead', '2016-06-11 00:00:00')

    INSERT INTO Title


    (WORKER_REF_ID, WORKER_TITLE, AFFECTED_FROM) VALUES
    (003, 'Lead', '2016-06-11 00:00:00')

    QUERIES
    1. SELECT GETDATE()
    2. SELECT FIRST_NAME AS WORKER_NAME FROM WORKER
    WORKER_NAME
    3. SELECT UPPER(FIRST_NAME) FROM Worker
    4. select distinct DEPARTMENT FROM Worker
    5. SELECT SUBSTRING(FIRST_NAME,1,3)FROM Worker
    6. SELECT DEPARTMENT, LENGTH(DEPARTMENT) 'Length of
    DEPARTMENT' FROM Worker;
    7. SELECT *FROM Worker ORDER BY FIRST_NAME
    8. SELECT *FROM Worker ORDER BY FIRST_NAME
    9. SELECT *FROM Worker WHERE FIRST_NAME='Saood'and
    LAST_NAME='Hashim'
    10. SELECT *FROM Worker WHERE DEPARTMENT='Admin'
    11. SELECT *FROM Worker WHERE FIRST_NAME LIKE '%a%'
    12. SELECT *FROM Worker WHERE FIRST_NAME LIKE '%__a%'
    13. SELECT FIRST_NAME FROM Worker WHERE
    length(FIRST_NAME) = 5 and FIRST_NAME like '%h';

    14.select count(worker_id) from worker where department


    ='Admin'
    15.select first_name from worker where salary between 50000 and
    100000
    16.select count(worker_id) as 'number of workers' from worker
    group by department order by department
    18. SELECT * FROM Worker)
    INTERSECT
    (SELECT * FROM title);
    19. SELECT * FROM Worker
    MINUS
    SELECT * FROM Title;
    20. select top 10 * from Worker
    22. SELECT * FROM Worker WHERE SALARY = (SELECT MIN(salary)
    FROM Worker);

    You might also like