vanisha0609 / dbms_ex_05

EXP No.5

Date:16/04/2024

AIM:

To Study & Implementation of Sub queries Views .

THEORY:

SUBQUERIES:

The query within another is known as a subquery. A statement containing a subquery is called a parent statement. The rows returned by subquery are used by the parent statement or in other words A subquery is a SELECT statement that is embedded in a clause of another SELECT statement You can place the subquery in a number of SQL clauses:

1. WHERE clause
2. HAVING clause
3. FROM clause
4. OPERATORS( IN.ANY,ALL,<,>,>=,<= etc..)

Types:

1. Sub queries that return several values

Sub queries can also return more than one value. Such results should be made use along with the operators in and any.

2. Multiple queries

Here more than one subquery is used. These multiple sub queries are combined by means of ‘and’ & ‘or’ keywords.

3. Correlated subquery

A subquery is evaluated once for the entire parent statement whereas a correlated Sub query is evaluated once per row processed by the parent statement.

VIEW:

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. A view is a virtual table, which consists of a set of columns from one or more tables. It is similar to a table but it does not store in the database. View is a query stored as an object.

Syntax:

CREATE VIEW AS SELECT FROM relation_name WHERE (Condition)

DROPPING A VIEW:

A view can be deleted with the DROP VIEW command.

Syntax:

DROP VIEW ;

MODULE:

QUESTION 1:

QUERY:

SELECT name,city
FROM customer
WHERE city in (SELECT city FROM customer WHERE id in (3,7))

OUTPUT:

QUESTION 2:

QUERY:

SELECT medication_id AS medic,medication_name,MIN(dosage) AS dosage
FROM Medications;

OUTPUT:

QUESTION 3:

QUERY:

WITH MinGrades AS (
    SELECT subject, MIN(grade) AS min_grade
    FROM Grades
    GROUP BY subject
)
SELECT g.student_id, g.student_name, g.subject, g.grade
FROM Grades g
JOIN MinGrades mg ON g.subject = mg.subject AND g.grade = mg.min_grade;

OUTPUT:

QUESTION 4:

QUERY:

SELECT *
FROM customer
WHERE customer_id=(SELECT salesman_id -2001
                   FROM salesman
                   WHERE name= 'Mc Lyon');

OUTPUT:

QUESTION 5:

QUERY:

SELECT id, name, city, email, phone
FROM customer
WHERE city <> (
    SELECT city
    FROM customer
    WHERE id = (SELECT MAX(id) FROM customer)
);

OUTPUT:

QUESTION 6:

QUERY:

SELECT ord_no,purch_amt,ord_date,customer_id,salesman_id
FROM orders
WHERE 
    purch_amt > (
        SELECT AVG(purch_amt)
        FROM orders
        WHERE ord_date = '2012-10-10'
    );

OUTPUT:

QUESTION 7:

QUERY:

SELECT id, name, age, city, income
FROM Employee
WHERE age < (
    SELECT AVG(age)
    FROM Employee
    WHERE income > 250000
);

OUTPUT:

QUESTION 8:

QUERY:

SELECT id, name, age, city, income
FROM Employee
WHERE age < (
    SELECT AVG(age)
    FROM Employee
    WHERE income > 250000
);

OUTPUT:

QUESTION 9:

QUERY:

SELECT name
FROM customer c1
WHERE NOT EXISTS (
    SELECT 1
    FROM customer c2
    WHERE c1.phone = c2.phone AND c1.id <> c2.id
);

OUTPUT:

QUESTION 10:

QUERY:

SELECT ord_no, purch_amt, ord_date, customer_id, salesman_id
FROM orders
WHERE salesman_id IN (
    SELECT salesman_id
    FROM orders
    WHERE customer_id = 3007
);

OUTPUT:

RESULT:

Thus,we studied & Implemented the Sub queries Views .