Snowflake Online Certification Training with Highly Experienced Trainers

Introduction to SQL. Covers DML with create and insert queries.

Hands on with various assignment activities

Display emp information who is getting sal more than 2500 working in 10 th dept
Display emp information whose name contains t in their emp name and his dept not belongs to 10 and 20
i) Add new column in emp table with department dname
ii)Update dname values with snowflake who belongs to 10 and 30 departments
Update employee salaries with 5000 whose length of the name contains more than 4 characters
Display emp information who is either deptno is 10 or comm is null and working as a mgr

select * from emp where salary_information>2500 and deptno = 10;
select * from emp where ENAME LIKE any ('%T%') and deptno not in (10,20) ;
i)alter table emp add DNAME varchar(255);
ii)UPDATE emp SET DNAME = 'snowflake' WHERE DEPTNO in (10,30);
update emp set salary_information = 5000 where length(ename) > 4;
select* from emp where job = 'MANAGER' and comm is null or deptno = 10 ;

Coming Soon

Covers update command, where clause.

Display emp information who is getting sal more than 2500 working in 10 th dept
Display emp information whose name contains t in their emp name and his dept not belongs to 10 and 20
i) Add new column in emp table with department dname
ii)Update dname values with snowflake who belongs to 10 and 30 departments
Update employee salaries with 5000 whose length of the name contains more than 4 characters
Display emp information who is either deptno is 10 or comm is null and working as a mgr ?
CREATE NEW TABLE EMP_DUP HAVING ALL UNIQUE RECORDS FROM THE EMP TABLE ?
DISPLAY ANNUAL SALARIES OF EMPLOYES ALONG WITH EMPNO AND SALARY DETAILS ?

select * from emp where sal > 2500 and deptno = 10;
select * from emp where ENAME LIKE '%T%' and deptno not in (10,20) ;
i)alter table emp add DNAME varchar(255);
ii)UPDATE emp SET DNAME = 'snowflake' WHERE DEPTNO in (10,30);
update emp set salary_information = 5000 where length(ename) > 4;
select* from emp where job = 'MANAGER' and comm is null or deptno = 10 ;
CREATE TABLE EMP_DUP SELECT DISTINCT * FROM EMP ;
SELECT ENAME,SAL,(NVL(SAL,0)+ NVL(COMM,0))*12 AS ANNUAL_SALARY FROM EMP;

NOTE : NVL AND COALESCE FUNCTIONS USED TO REPLACE NULL VALUES WITH REQUIRED REPLACED VALUE , HERE REPLACING NULL WITH 0
EXAMPLE OF NVL: NVL(COMM,0) --> WHICH IS REPLACING NULL VALUES WITH '0'

EXAMPLE OF COALESCE : COALESCE(COLUMN1,COLUMN2,0) --> COLUMN1 IS NULL THEN REPLACING WITH COLUMN2 ,COLUMN2 ALSO NULL REPLACE WITH '0'

Coming Soon

Introduction to SQL aggregate functions
SQL Aggregate Function
An aggregate function allows you to perform a calculation on a set of values to return a single value.

We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

The following are the most commonly used SQL aggregate functions:
AVG – calculates the average of a set of values.
COUNT – counts rows in a specified table or view.
MIN – gets the minimum value in a set of values.
MAX – gets the maximum value in a set of values.
SUM – calculates the sum of values.

Notice that all aggregate functions above ignore NULL values except for the COUNT function.

DISPLAY DEPARTMENT WISE AVERAGE SALARIES WORKING IN 10 AND 20 DEPARTMENT'S GETTING SALARY MORE THAN 1800 IN EMP TABLE?

DISPLAY UNIQUE RECORDS FROM EMPLOYEE TABLE WITH OUT USING DISTINCT KEYWORD ?

DISPLAY DUPLICATE RECORDS FROM EMPLOYEE TABLE WHO IS WORKING IN 30TH DEPARTMENT AND THERE RESPECTIVE COMMISION IS NOT NULL ?

DISPLAY JOB WISE AVERAGE SALARIES WHOSE AVERAGE SALARY IS MORE THAN 2100 ?

DISPLAY DEPARTMENT WISE SUM OF SALARIES WHOSE SALARY IS MORE THAN 800 AND GETTING SUM OF SALARY LESS THAN 1500 ?

DISPLAY DUPLICATE RECORDS BASED ON ALL COLUMNS FROM EMPLOYEE TABLE ?

EMPNO	ENAME	SAL	DEPTNO
1	A	1000	10
1	A	1000	20
2	B	5800	20
3	C	800	10
4	D	100	10
4	D	100	10

select deptno, avg(sal) as average_salary from emp where deptno in(10,20) and sal >1800 group by deptno;

select empno, ename, job, mgr, hiredate, sal,comm,deptno from emp group by empno, ename, job, mgr, hiredate, sal,comm,deptno ;

select empno, ename, job, mgr, hiredate, sal,comm,deptno from emp where deptno = 30 and comm is not null group by empno, ename, job, mgr, hiredate, sal,comm,deptno having count(*)>1;

select job, avg(sal) from emp group by job having avg(sal) >2100;

select deptno , sum(sal) from emp where sal>800 group by deptno having sum(sal)<1500;

Select empno, ename, job, mgr, hiredate, sal,comm,deptno, count(*) From emp Group by empno, ename, job, mgr, hiredate, sal,comm,deptno Having count(*)>1 ;

Coming Soon

Introduction to SQL PARTITION BY CLAUSE
ADVANTAGE OF PARTITION BY COMPARED WITH GROUP BY CLAUSE
It will cover the SQL PARTITION BY clause and, in particular, the difference with GROUP BY in a select statement. We will also explore various use cases of SQL PARTITION BY.

We use SQL PARTITION BY to divide the result set into partitions and perform computation on each subset of partitioned data..

We use SQL GROUP BY clause to group results by specified column and use aggregate functions such as Avg(), Min(), Max() to calculate required values.
AVG – calculates the average of a set of values.
COUNT – counts rows in a specified table or view.
MIN – gets the minimum value in a set of values.
MAX – gets the maximum value in a set of values.
SUM – calculates the sum of values.

Notice that all aggregate functions above ignore NULL values except for the COUNT function.

DISPLAY RESPECTIVE DEPARTMENT WISE AVERAGE SALARIES WORKING IN 10 AND 20 DEPARTMENT'S GETTING SALARY MORE THAN 1800 ALONG WITH EMPNO,ENAME,SAL,DEPTNO AND COMM IN EMP TABLE?

DISPLAY UNIQUE RECORDS FROM EMPLOYEE TABLE WITH OUT USING DISTINCT KEYWORD ?

DISPLAY DUPLICATE RECORDS FROM EMPLOYEE TABLE WHO IS WORKING IN 30TH DEPARTMENT AND THERE RESPECTIVE COMMISION IS NOT NULL ?

DISPLAY JOB WISE AVERAGE SALARIES WHOSE AVERAGE SALARY IS MORE THAN 2100 ?

DISPLAY DEPARTMENT WISE SUM OF SALARIES WHOSE SALARY IS MORE THAN 800 AND GETTING SUM OF SALARY LESS THAN 1500 ?

DISPLAY DUPLICATE RECORDS BASED ON ALL COLUMNS FROM EMPLOYEE TABLE ?

select EMPNO,ENAME,SAL,DEPTNO,COMM, avg(sal) OVER (PARTITION BY DEPTNO) as average_salary from emp where deptno in(10,20) and sal >1800 ;

select empno, ename, job, mgr, hiredate, sal,comm,deptno from emp group by empno, ename, job, mgr, hiredate, sal,comm,deptno ;

select empno, ename, job, mgr, hiredate, sal,comm,deptno from emp where deptno = 30 and comm is not null group by empno, ename, job, mgr, hiredate, sal,comm,deptno having count(*)>1;

select job, avg(sal) from emp group by job having avg(sal) >2100;

select deptno , sum(sal) from emp where sal>800 group by deptno having sum(sal)<1500;

Select empno, ename, job, mgr, hiredate, sal,comm,deptno, count(*) From emp Group by empno, ename, job, mgr, hiredate, sal,comm,deptno Having count(*)>1 ;

Coming Soon

Introduction to SQL SUBQUERYS
Using a Subquery to Solve a Problem in EMP Table (Created earlier classes Refer class-01) "Who has a salary greater than Jones'?"
"Which employees have a salary greater than Jones' salary?".
"Employee details who is getting maximum salary?"

You can place the subquery in a number of SQL clauses

WHERE clauss
HAVING clause
FROM clause

Types of Subqueries

Single-row subquery
Multiple-row subquery
Multiple-column subquery

Display all employee information who is getting maximum salary .?

Write a query to display the employee name and hiredate for all employees in the same department as Blake. Exclude Blake.?

Create a query to display the employee number and name for all employees who earn more than the average salary. Sort the results in descending order of salary. ?

. Write a query to display the employee number and name for all employees who work in a department with any employee whose name contains T. ?

Display the employee name, department number, and job title for all employees whose department location is Dallas ?

Display the employee name and salary of all employees who report to King. ?

Display the department number, name,, and job for all employees in the Sales department.?

display the employee number, name, and salary for all employees who earn more than the average salary and who work in a department with any employee with T in their name..?

select * from emp where sal = (select max(sal) from emp);

SELECT ename, hiredate FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'BLAKE') AND ename <> 'BLAKE'; ;

SELECT empno, ename FROM emp WHERE sal > ( SELECT AVG(sal) FROM emp );

SELECT empno, ename FROM emp WHERE deptno IN ( SELECT deptno FROM emp WHERE ename LIKE '%T%' );

SELECT ename, empno, job FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc ='DALLAS') ;

SELECT e.ename, e.sal FROM emp e , emp d WHERE e.mgr = d.empno AND d.ename ='KING';
Solution with subquery:
SELECT ename, sal FROM emp WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING' );

SELECT e.deptno, e.ename, e.job , d.dname FROM emp e , dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES'

SELECT empno, ename , sal FROM emp WHERE sal > (SELECT AVG (sal) FROM emp ) AND deptno IN ( SELECT deptno FROM emp WHERE ename LIKE '%T%');

Recording : Coming soon

Introduction to SET OPERATORS
UNION EXAMPLE :
SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM EMP UNION SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM DUP_EMP;

SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM EMP UNION ALL SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM DUP_EMP;

CREATE TABLE UNION_DATA AS:
( ( SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM EMP.
WHERE DEPTNO=10 .
WHERE DEPTNO=10 .
UNION ALL.
SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM DUP_EMP.

);.

INSERCT

SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM EMP ?

INTERSECT ?

SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM DUP_EMP; ?

--->1 .COUNT COMPARISION

---->2. CHECK DUPLICATE RECORDS IN TARGET TABLE ?

---->2. CHECK DUPLICATE RECORDS IN TARGET TABLE ?	MINUS
1	SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM EMP	MINUS	SELECT EMPNO,ENAME,DEPTNO,SAL,COMM FROM DUP_EMP;
	CREATE TABLE S2 (ID INT,NAME VARCHAR,LOC VARCHAR);
		INSERT INTO S2 VALUES(1,'AA','BGLR');
		SELECT * FROM S2;	SELECT * FROM S1
UNION		SELECT * FROM S2;	CREATE TABLE T1 (ID INT,NAME VARCHAR,LOC VARCHAR);

CREATE TABLE T1 (ID INT,NAME VARCHAR,LOC VARCHAR);;

UPDATE T1 SET NAME = 'ABA' WHERE NAME ='AA';

INSERT INTO T1 ;

( SELECT * FROM S1 UNION SELECT * FROM S2)

S1 ,S2 ------------->T1;

S1 ,S2 ------------->T1 (SELECT ID,NAME,LOC FROM S1 UNION SELECT ID,NAME,LOC FROM S2); ---------------------------------------------- (SELECT ID,NAME,LOC FROM S1 UNION SELECT ID,NAME,LOC FROM S2) MINUS SELECT ID,NAME,LOC FROM T1 ; Having count(*)>1 ;

Coming Soon

CH CloudHill

SQL: Day Wise Course details

Class-01

Class-02

Class-03

Class-04

Class-05

Class-06

Class-07

Class-08

Class-09

Class-10

Class-11

Class-12

Class-13

Class-14