SQL QURIES - PART 2
ORDER TABLE DESIGN
/*The SQL CASE Statement
The CASE statement goes through conditions and returns a value when the first condition is met
(like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and
return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.*/
/* CASE Syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END; */
SELECT OrderAmount,
CASE
WHEN OrderAmount >500 then 'quality is good'
WHEN OrderAmount >250 then 'quality is beeter'
WHEN OrderAmount <50 then 'quality is best'
ELSE 'OrderAmount is not visible'
end
from Orders
/*SQL CASE Examples
The following SQL will order the customers by Firstname. However, if age is NULL, then order by Address:*/
SELECT FirstName, Address
FROM Customer
ORDER BY
(CASE
WHEN Age IS NULL THEN Address
ELSE LastName
END);
/*
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to the
column names in the first SELECT statement in the UNION.
*/
/* The following SQL statement returns the cities (only distinct values)
from both the "Customers" and the "Suppliers" table:*/
/* UNION selects only distinct values. Use UNION ALL to also select duplicate values*/
Select OrderAmount from Customer
Union
Select OrderAmount from Orders
Select OrderAmount from Customer
Union All
Select OrderAmount from Orders
/* The following SQL statement returns the London cities
(only distinct values) from both the "Customers" and the
"Order" table:*/
select OrderAmount from Customer
where Country = 'London'
Union
select OrderAmount from Orders
where Country = 'London'
Order by OrderAmount;
select OrderAmount from Customer
where Country = 'London'
Union all
select OrderAmount from Orders
where Country = 'London'
Order by OrderAmount;
/*The GROUP BY clause allows you to arrange the rows of a query in groups.
The groups are determined by the columns that you specify in the GROUP BY clause.*/
/* SELECT
select_list
FROM
table_name
GROUP BY
column_name1,
column_name2 ,...;*/
/*SELECT
column_1,
column_2,
...,
aggregate_function(column_3)
FROM
table_name
GROUP BY
column_1,
column_2,
...; */
/*The SQL GROUP BY Statement
The GROUP BY statement is often used with aggregate functions
(COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.*/
/* GROUP BY Syntax */
/*SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s); */
/* The following SQL statement lists the number of customers in each country: */
SELECT COUNT(CustomerID), Country
FROM Customer
GROUP BY Country;
/* The following SQL statement lists the number of customers in each country, sorted high to low */
SELECT COUNT(CustomerID), Country
FROM Customer
GROUP BY Country
ORDER BY COUNT(CustomerID) ASC;
/*
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword
could not be used with aggregate functions.*/
/* SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
*/
SELECT COUNT(CustomerID), Country
FROM Customer
GROUP BY Country
HAVING COUNT(CustomerID) > 2;
/* The following SQL statement lists the number of customers in
each country, sorted high to low (Only include countries with more than 5 customers):*/
SELECT COUNT(CustomerID), Country
FROM Customer
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
/* The ANY and ALL operators are used with a WHERE or HAVING clause.
The ANY operator returns true if any of the subquery values meet the condition.
The ALL operator returns true if all of the subquery values meet the condition
*/
/* ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
ALL Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
*/
/* The ANY operator returns TRUE if any of the subquery values meet the condition. */
Select Country from Orders where OrderAmount = ANY (select OrderAmount FROM Orders where CustomerID = 2)
/* The Above SQL statement returns TRUE and lists the customerID if it finds
ANY records in the Order table that CustomerID = 1:*/
/* The ALL operator returns TRUE if all of the subquery values meet the condition. */
SELECT FirstName
FROM Customer
WHERE CustomerID = ALL (SELECT OrderAmount FROM Orders WHERE Age>30);
/* Alias of coulmn name */
Select o.OrderID , c.CustomerID, c.FirstName, o.OrderAmount from Customer as c, Orders as o
where c.CustomerID = o.CustomerID;
/* • There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
Two or more columns are combined together */
/* SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of the query.*/
/* Alias for Column */
/* SELECT column_name AS alias_name
FROM table_name; */
Select FirstName as F from Customer /* Here F is Alias for column name "FirstName".*/
/* The following SQL statement creates aliases, for the FirstName column
and Note: It requires double quotation marks
or square brackets if the alias name contains spaces: */
Select FirstName as [First Name] from Customer
/* The following SQL statement creates an alias named "Country" that combine 3 columns */
select FirstName , LastName + ',' + Country + ',' + Address as Country from Customer;
/*The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns true if the subquery returns one or more records */
/* EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition) */
select Address from Customer where Exists (select OrderAmount from Customer where OrderAmount >100);
CONTINUE..... SQL NEXT PART CLICK HERE
Comments
Post a Comment