To Study & Implementation of Sub queries Views .
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:
- WHERE clause
- HAVING clause
- FROM clause
- OPERATORS( IN.ANY,ALL,<,>,>=,<= etc..)
Sub queries can also return more than one value. Such results should be made use along with the operators in and any.
Here more than one subquery is used. These multiple sub queries are combined by means of ‘and’ & ‘or’ keywords.
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.
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.
CREATE VIEW AS SELECT FROM relation_name WHERE (Condition)
A view can be deleted with the DROP VIEW command.
DROP VIEW ;
SELECT name,city
FROM customer
WHERE city in (SELECT city FROM customer WHERE id in (3,7))
SELECT medication_id AS medic,medication_name,MIN(dosage) AS dosage
FROM Medications;
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;
SELECT *
FROM customer
WHERE customer_id=(SELECT salesman_id -2001
FROM salesman
WHERE name= 'Mc Lyon');
SELECT id, name, city, email, phone
FROM customer
WHERE city <> (
SELECT city
FROM customer
WHERE id = (SELECT MAX(id) FROM customer)
);
SELECT department_id AS depar, department_name
FROM Departments
WHERE LENGTH(department_name) > (
SELECT AVG(LENGTH(department_name))
FROM Departments
);
SELECT id, name, age, city, income
FROM Employee
WHERE age < (
SELECT AVG(age)
FROM Employee
WHERE income > 250000
);
SELECT id, name, age, city, income
FROM Employee
WHERE age < (
SELECT AVG(age)
FROM Employee
WHERE income > 250000
);
SELECT name
FROM customer c1
WHERE NOT EXISTS (
SELECT 1
FROM customer c2
WHERE c1.phone = c2.phone AND c1.id <> c2.id
);
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
);
Thus,we studied & Implemented the Sub queries Views .