Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    Lab 16 02

    Uploaded by

    Amrutha Varsha
    st lab

    Copyright:

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

    Lab 16 02

    Uploaded by

    Amrutha Varsha
    4 views10 pages
    st lab

    Original Title

    lab_16_02

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    st lab

    Copyright:

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

    Lab 16 02

    Uploaded by

    Amrutha Varsha
    st lab

    Copyright:

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

    WK3

    4. Table

    CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    employee_name VARCHAR(255),

    department_id INT,

    job_id VARCHAR(50),

    salary DECIMAL(10, 2)

    );

    a. Count the number of employees in department 20:

    SELECT COUNT(*)

    FROM employees

    WHERE department_id = 20;

    b. Find the minimum salary earned by clerks:

    SELECT MIN(salary)

    FROM employees

    WHERE job_id = 'CLERK';


    c. Find minimum, maximum, and average salary of all employees:

    SELECT MIN(salary), MAX(salary), AVG(salary)

    FROM employees;

    d. List the minimum and maximum salaries for each job type:

    SELECT job_id, MIN(salary), MAX(salary)

    FROM employees

    GROUP BY job_id;

    e. List the employee names in descending order:

    SELECT employee_name

    FROM employees

    ORDER BY employee_name DESC;

    f. List the employee id, names in ascending order by empid:

    SELECT employee_id, employee_name

    FROM employees

    ORDER BY employee_id ASC;

    5. Tables
    CREATE TABLE sailors (
    sid INT PRIMARY KEY,

    sname VARCHAR(255),
    age INT,
    rating INT
    );

    CREATE TABLE boats (


    bid INT PRIMARY KEY,
    bname VARCHAR(255),
    color VARCHAR(50)

    );

    CREATE TABLE reservations (


    sid INT,
    bid INT,

    reservation_date DATE,
    PRIMARY KEY (sid, bid),
    FOREIGN KEY (sid) REFERENCES sailors(sid),
    FOREIGN KEY (bid) REFERENCES boats(bid)
    );
    a. Find the sids, names of sailors who have reserved all boats called “INTERLAKE”:

    SELECT s.sid, s.sname


    FROM sailors s
    WHERE NOT EXISTS (

    SELECT *
    FROM boats b
    WHERE b.bname = 'INTERLAKE'
    AND NOT EXISTS (
    SELECT *

    FROM reservations r
    WHERE r.bid = b.bid
    AND r.sid = s.sid
    )
    );

    b. Find the sname, bid, and reservation date for each reservation:

    SELECT s.sname, r.bid, r.reservation_date


    FROM sailors s
    JOIN reservations r ON s.sid = r.sid;

    c. Find the ages of sailors whose name begins and ends with B and has at least 3 characters:

    SELECT s.age
    FROM sailors s
    WHERE s.sname LIKE 'B%B' AND LENGTH(s.sname) >= 3;

    d. List in alphabetic order all sailors who have reserved a red boat:

    SELECT s.sname
    FROM sailors s
    JOIN reservations r ON s.sid = r.sid
    JOIN boats b ON r.bid = b.bid
    WHERE b.color = 'red'

    ORDER BY s.sname ASC;

    e. Find the age of the youngest sailor for each rating level:

    SELECT rating, MIN(age)


    FROM sailors

    GROUP BY rating;

    6. Tables
    CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,

    ProductType VARCHAR(255),
    Price DECIMAL(10, 2),
    OrderDate DATE,
    Vendor VARCHAR(255),
    VendorType VARCHAR(50)
    );

    CREATE TABLE Vendors (


    VendorID INT PRIMARY KEY,
    VendorName VARCHAR(255),
    VendorType VARCHAR(50)
    );

    a. List the Vendors who have delivered products within 6 months from order date:

    SELECT Vendor
    FROM Orders
    WHERE DATEDIFF(MONTH, OrderDate, GETDATE()) <= 6;
    b. Display the Vendor details who have supplied both Assembled and Subparts:

    SELECT Vendor
    FROM Orders
    GROUP BY Vendor

    HAVING COUNT(DISTINCT ProductType) > 1;

    c. Display the Sub parts by grouping the Vendor type (Local or NonLocal):

    SELECT ProductType, VendorType


    FROM Orders

    GROUP BY ProductType, VendorType;

    d. Display the Vendor details in ascending order:

    SELECT *
    FROM Vendors
    ORDER BY Vendor ASC;

    e. Display the Sub part which costs more than any of the Assembled parts:

    SELECT ProductType
    FROM Orders
    WHERE Price > ALL (SELECT Price FROM Orders WHERE ProductType = 'Assembled');

    f. Display the second maximum cost Assembled part:

    SELECT MAX(Price)
    FROM Orders
    WHERE ProductType = 'Assembled'
    AND Price NOT IN (SELECT MAX(Price) FROM Orders WHERE ProductType = 'Assembled');
    WK 4
    a. PL/SQL program to swap two numbers:

    DECLARE

    num1 NUMBER := 10;

    num2 NUMBER := 20;

    temp NUMBER;

    BEGIN

    DBMS_OUTPUT.PUT_LINE('Before swapping: num1 = ' || num1 || ', num2 = ' || num2);

    temp := num1;

    num1 := num2;

    num2 := temp;

    DBMS_OUTPUT.PUT_LINE('After swapping: num1 = ' || num1 || ', num2 = ' || num2);

    END;

    b. PL/SQL program to find the largest of three numbers:

    DECLARE
    num1 NUMBER := 10;
    num2 NUMBER := 20;
    num3 NUMBER := 15;
    max_num NUMBER;

    BEGIN
    IF num1 >= num2 AND num1 >= num3 THEN
    max_num := num1;
    ELSIF num2 >= num1 AND num2 >= num3 THEN
    max_num := num2;
    ELSE
    max_num := num3;
    END IF;

    DBMS_OUTPUT.PUT_LINE('Largest number: ' || max_num);


    END;

    2.
    a. PL/SQL program to find the total and average of 6 subjects and display the grade:

    DECLARE
    subject1 NUMBER := 80;
    subject2 NUMBER := 75;
    subject3 NUMBER := 85;
    subject4 NUMBER := 90;

    subject5 NUMBER := 78;


    subject6 NUMBER := 82;
    total NUMBER;
    average NUMBER;
    grade VARCHAR2(2);

    BEGIN
    total := subject1 + subject2 + subject3 + subject4 + subject5 + subject6;
    average := total / 6;

    IF average >= 90 THEN

    grade := 'A';
    ELSIF average >= 80 THEN
    grade := 'B';
    ELSIF average >= 70 THEN
    grade := 'C';
    ELSIF average >= 60 THEN
    grade := 'D';
    ELSE
    grade := 'F';

    END IF;

    DBMS_OUTPUT.PUT_LINE('Total: ' || total);


    DBMS_OUTPUT.PUT_LINE('Average: ' || average);
    DBMS_OUTPUT.PUT_LINE('Grade: ' || grade);

    END;

    b. PL/SQL program to find the sum of digits in a given number:

    DECLARE

    num NUMBER := 12345;


    digit NUMBER;
    sum NUMBER := 0;
    BEGIN
    WHILE num > 0 LOOP

    digit := MOD(num, 10);


    sum := sum + digit;
    num := num / 10;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Sum of digits: ' || sum);


    END;

    You might also like