The most commonly used SQL command is SELECT statement. It is used to query the database and retrieve selected data that follow the conditions we want. In simple words, we can say that the select statement used to query or retrieve data from a table in the database.
Select the list of column from a table
The select command is used to select the data from the database and display it to the user.
select particular fields from the table.
SELECT
column1
,column2
,column3
FROM tables;
Select all fields from Table.
SELECT * FROM Table;
If we want to display certain field without any duplicates then we use the DISTINCT keyword along with the select command.
SELECT Distinct Fname FROM Table;
If we need only certain records from the table then we use the where clause. Where clause acts as a Filtering mechanism. Under the Where section we need to specify certain conditions, only if those conditions are met the records will be extracted.
SELECT
FName
,LName
,Address
,RollNo
FROM Students
WHERE City='Delhi';
Here expression is column that we want retrieve.
Tables indicate for where we want retrieve the data.
Optional clauses in SELECT statement
There are some optional clauses in SELECT statement:
[WHERE Clause] : It specifies which rows to retrieve.
[GROUP BY Clause] : Groups rows that share a property so that the aggregate function can be applied to each group.
[HAVING Clause] : It selects among the groups defined by the GROUP BY clause.
[ORDER BY Clause] : It specifies an order in which to return the rows.
SQL WHERE
A WHERE clause in SQL is a data manipulation language statement. WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query. Actually. it filters the records. It returns only those queries which fulfill the specific conditions. WHERE clause is used in SELECT, UPDATE, DELETE statement etc.
Let's see the syntax for sql where:
SELECT column1, column 2, ... column n
FROM table_name
WHERE [conditions]
WHERE clause uses some conditional selection
= equal
> greater than
< less than
>= greater than or equal
<= less than or equal
< > not equal to
create volatile table sales
(
store_no integer,
sale_month date,
prod_code char (20) character set latin not casespecific,
projected_sale integer,
actual_sale integer
)
primary index ( store_no )
on commit preserve rows;
insert into sales values(10,'2007-01-01','Apple',15000,14500);
insert into sales values(10,'2007-01-01','Apple',15000,13500);
insert into sales values(20,'2007-02-01','Banana',10000,11000);
insert into sales values(30,'2007-03-01','Orange',11000,10500);
insert into sales values(40,'2007-04-01','Apricot',12500,12500);
insert into sales values(50,'2007-05-01','Grapes',15000,12500);
insert into sales values(60,'2007-06-01','Apple',12000,11500);
insert into sales values(70,'2007-07-01','Banana',15500,11500);
insert into sales values(80,'2007-07-01','Grapes',15000,15500);
insert into sales values(90,'2007-06-01','Orange',16000,15500);
insert into sales values(10,'2007-05-01','Apricot',14500,13500);
insert into sales values(20,'2007-04-01','Mango',15500,12500);
insert into sales values(30,'2007-03-01','Mango',15000,12500);
insert into sales values(40,'2007-02-01','Guava',14000,11500);
insert into sales values(50,'2007-01-01','Guava',12000,10500);
insert into sales values(30,'2007-02-01','Strawberry',12000,10000);
create volatile table customers
(
cust_num int,
store_no integer,
product_id varchar(20)
)
primary index ( cust_num )
on commit preserve rows;
select * from customers;
insert into customers values(101,10,'ABC10');
insert into customers values(102,10,'ABC11');
insert into customers values(103,20,'ABC10');
insert into customers values(104,30,'ABC10');
insert into customers values(105,20,'ABC10');
insert into customers values(106,10,'ABC10');
select
c.cust_num
,c.store_no
,c.product_id
,s.store_no
,s.sale_month
,s.prod_code
,s.projected_sale
,s.actual_sale
from customers c
left join sales s
on c.store_no=s.store_no
where c.store_no in (10)
select
store_no
,sum(actual_sale) as total_sale
from sales
group by store_no
order by store_no;
To find the row number,rank,dense_Rank?
prod_code
,actual_sale,
row_number() over ( order by actual_sale ) as rownum,
rank() over ( order by actual_sale ) as rnk,
dense_rank() over ( order by actual_sale ) as denserank
from sales;
select prod_code ,actual_sale,
row_number() over (partition by prod_code order by actual_sale ) as rownum,
rank() over (partition by prod_code order by actual_sale ) as rnk,
dense_rank() over (partition by prod_code order by actual_sale ) as denserank
from sales;
OLAP FUNCTION
OLAP is oftten called ordered Analytics. OLAP function SORT all the rows before doing any calculation.
CSUM-Cumulative SUM
SEL * FROM TDUSER.Sales_Table;
SELECT
,PRODUCT_ID
, SALE_DATE
, DAILY_SALES
,CSUM(DAILY_SALES, SALE_DATE) AS "CSUM"
FROM SALES_TABLE ;
Steps:-
1. First it will sort the the table based on the column SALES_DATE.
2. Now it will give the cumulative of the column DAILY_SALES.
SELECT
PRODUCT_ID
, SALE_DATE
, DAILY_SALES
,CSUM(DAILY_SALES,PRODUCT_ID, SALE_DATE) AS "CSUM"
FROM SALES_TABLE ;
CSUM to get Sequence number
SELECT
PRODUCT_ID
,SALES_DATE
,DAILY_SALES
CSUM(1,PRODUCT_ID) AS "SEQUENCE_NUMBER"
FROM SALES_TABLES;
How to use extract function with date and time column
SELECT EXTRACT (YEAR FROM date);
SELECT EXTRACT (DAY FROM date);
SELECT EXTRACT (MINUTE FROM time);
SELECT EXTRACT (HOUR FROM time) ;
SELECT EXTRACT (SECOND FROM time) ;
SELECT First_Name, Last_Name
FROM employee
WHERE Last_Name LIKE ALL (‘%Sim%’,’ %Pet%’)
SELECT First_Name, Last_Name
FROM employee
WHERE Last_Name LIKE (‘%S\_%’) ESCAPE ‘\’
In SQL, what’s the difference between the having clause and the group by statement?
In SQL, the having clause and the group by statement work together when using aggregate functions like SUM, AVG, MAX, etc. This is best illustrated by an example. Suppose we have a table called emp_bonus as shown below. Note that the table has multiple entries for employees A and B – which means that both employees A and B have received multiple bonuses.
emp_bonus
Employee
Bonus
A
1000
B
2000
A
500
C
700
B
1250
If we want to calculate the total bonus amount that each employee has received, then we would write a SQL statement like this:
select employee, sum(bonus) from emp_bonus group by employee;
The Group By Clause
In the SQL statement above, you can see that we use the "group by" clause with the employee column. The group by clause allows us to find the sum of the bonuses for each employee – because each employee is treated as his or her very own group. Using the ‘group by’ in combination with the ‘sum(bonus)’ statement will give us the sum of all the bonuses for employees A, B, and C.
Running the SQL above would return this:
Employee
Sum(Bonus)
A
1500
B
3250
C
700
Now, suppose we wanted to find the employees who received more than $1,000 in bonuses for the year of 2012 – this is assuming of course that the emp_bonus table contains bonuses only for the year of 2012. This is when we need to use the HAVING clause to add the additional check to see if the sum of bonuses is greater than $1,000, and this is what the SQL look like:
GOOD SQL:
select employee, sum(bonus) from emp_bonus
group by employee having sum(bonus) > 1000;
And the result of running the SQL above would be this:
Employee
Sum(Bonus)
A
1500
B
3250
Difference between having clause and group by statement
So, from the example above, we can see that the group by clause is used to group column(s) so that aggregates (like SUM, MAX, etc) can be used to find the necessary information. The having clause is used with the group by clause when comparisons need to be made with those aggregate functions – like to see if the SUM is greater than 1,000, as in our example above. So, the having clause and group by statements are not really alternatives to each other – but they are used alongside one another!
Count validations: in source and target?
Select Count(*) form Stab minus select count(*) from Ttab;
Duplicate records to be found?
Select empid, ename, sal,count(*) from emp group by empid, ename,sal having count(*) >1;
Delete the duplicate records in the table?
DELETE FROM SALES WHERE STORE_NO IN (SELECT STORE_NO FROM SALES WHERE STORE_NO=10)
Delete from Ttab where rowid not in ( select max(rowid) from Ttab group by empid, ename, sal having count(*) >1)
What is coalesce function?
Select coalesce(sal,comm,0) from emp;
What is NVL function, decode, case, replace functions?
Select nvl(comm,0) from emp;
Select decode(comm,0,12) from emp;
Select case comm when comm <10 then 10
when comm >20 and comm <30 then 20
else 40
end
To find the 4th highest salary of emp?
select * from emp e1 where 4=(select count (distinct salary) from emp e2 where e1.slary < e2.salary.
7. To find the last 10 records in the table?
8. To find the first 10 records in the table?
9. To find the 10 -30 records in the table?
10. To find the Rank and dense rank functions, write?
11. To find the records contain the NULL in the table?
12. Write the SQL to data validations, minus query?
13. Write the union , union all queries?
14. Write the self join to find the employees and his managers names?
15. Write the root of the managers and its childs structure?
16. Write to find the max salary of every department ?
17. Write the left join, right join and full outer join queries?
18. Write to find the user ids in the email id of employees?
19. Write pivot query?
20. Write unpivot query?
21. Merge query?
22. Write to display row to column?
23. Write to display column to row?
24. Write to display the age of employee?
25. Write to display sum of order amount for the quarter of all the orders.
25. Write to display last day of the month?
26. Write to display first day of the month?
27. Write to display months between two dates?
28. Write to display the date in the format of 09/Aug/2017?
29. Write to display the date in the format of 09/Aug/2017 10:15:11
6. Select sal from (select sal, rownum rown1 from emp order by sal desc ) where rown1=4;
7. Select e.* from emp e where 4>= (Select count(e1.rowid) from emp e1 where e.rowid <=e1.rowid)
8. Select e.* from emp e where 4<=(select count(e.rowid) from emp e1 where e.rowid>=e1.rowid)
9. (select e.* from emp e where rownum<=30) minus (select e1.* from emp e1 where rownum>=9)
10. Select deptno, sal, rank() over(partitioned by deptno, order by sal desc) from emp e
select deptno, sal, dense_rank() over(partitioned by deptno, order by sal asc) from emp e
11. Select * from emp where empid is null or ename is null or sal is null;
12. Select deptno, sum(sal) from emp e group by deptno
minus
select * from Tsal_fact
13. Select empid, ename, sal from dell d union select empid, ename, sal from emphasis
Select empid, ename, sal from dell d union all select empid, ename, sal from emphasis
14. select e.ename employee name, m.ename manager name from employee e, employee m where e.managerid = m.empid
16. select deptno, max(sal) from emp group by deptno;
18. Select substr(email, 1, substr(email,'@',1,1)-1) from emp;
No comments:
Post a Comment