CH CloudHill

SQL Daily Learning

1.DISPLAY EMPLOYEE DETAILS WORKING IN 10 AND 20 DEPARTMENTS ?

SELECT * FROM EMP WHERE DEPTNO IN (10,20);

2.DISPLAY EMPLOYEE DETAILS GETTING COMMISION AS NULL ?

SELECT * FROM EMP WHERE COMM IS NULL;

3.DISPLAY EMPLOYEE DETAILS GETTING SALARY MORE THAN 3000 WORKING IN 10 DEPARTMENT ?

SELECT * FROM EMP WHERE SAL > 3000 AND DEPTNO = 10;

4.CREATE NEW BACKUP TABLE BKP_EMP FROM EMP TABLE ?

CREATE TABLE BKP_EMP AS SELECT * FROM EMP;

5.UPDATE SALARY 3500 IN BKP_EMP TABLE WHOSE COMMISON IS NULL ?

UPDATE EMP SET SAL = 3500 WHERE COMM IS NULL;

6.CREATE NEW TABLE BKP_EMP_1 HAVING ONLY 10 AND 20 DEPT INFORMATION?

CREATE OR REPLACE TABLE BKP_EMP_1 AS SELECT * FROM BKP_EMP WHERE DEPTNO IN(10,20);

7.CREATE NEW TABLE BKP_EMP_2 HAVING ONLY METADATA INFORMATION (EMPTY NEW TABLE)?

ANS 1 : CREATE OR REPLACE TABLE BKP_EMP_2 AS SELECT * FROM EMP WHERE 1 = 2; (OR) ANS 2 : CREATE OR REPLACE TABLE BKP_EMP_2 LIKE EMP;

8.DIFFRENCE BETWEEN DELETE DROP AND TRUNCATE ? WHICH IS FASTER ?

DELETE --> DELETING ROWS/CONDITION BASED ROWS FROM TABLE.
TRUNCATE--> FASTER THAN DELETE AND DELETE ENTIRE SET OF ROWS.

DROP --> DELETE AS WELL AS META DATA FROM DATABASE PERMANENTLY.


9.ADD NEW COLOUMN ANNUAL_SAL TO EMP TABLE ?

ALTER TABLE EMP ADD ANNUAL_SAL INTEGER ;

10.UPDATE VALUES FOR ANNUAL_SAL COLOUMN WITH (SAL+COMM)*12 LOGIC IN EMP TABLE ?

UPDATE EMP SET ANNUAL_SAL=(SAL+NVL(COMM,0))*12;

11.HOW TO DISPLAY FIRST CHARACTER CAPITAL NAMES IN EMPLOYEE NAME ? Example: Snowflake

SELECT INITCAP(ENAME) AS FULL_NAME FROM EMP;

12.HOW TO DISPLAY FIRST 3 CHARACTERS FROM EMPLOYEE NAME ?

SELECT SUBSTRING(ENAME,1,3) AS EMPNAME FROM EMP;

13.DISPLAY EMPLOYEE DETAILS WHOSE NAME CONTAINS T ?

SELECT * FROM EMP WHERE ENAME LIKE '%T%';

14.DISPLAY EMPLOYEE DETAILS WHOSE SECOND CHARACTER CONTAINS O ?

SELECT * FROM EMP WHERE ENAME LIKE '_O%';

15.DISPLAY EMPLOYEE DETAILS WHOSE NAME CONTAINS MORE THAN FOUR CHARACTERS ?

SELECT * FROM EMP WHERE LENGTH(ENAME)>4;

16. HOW TO DISPLAY VALUES LIKE JONES|1000|10 ALONG WITH EMPLOYEE DETAILS ? (EXAMPLE:: ENAME | SAL | DEPTNO )

SELECT CONCAT(ENAME,'|' ,SAL, '|',DEPTNO) AS CONCATENATE_NAME FROM EMP;

17.DISPLAY EMPLOYEE DETAILS WITH FIRST TWO CHARACTERS ALONG WITH LAST CHARACTER IN ENAME FROM EMP TABLE ? (EXAMPLE : ENAME :SNOWFLAKE THEN OUTPUT LIKE SNE)

Multiple ways to implement logic for above Question; Understand SUBSTR,LEFT,RIGHT,CHARINDEX functions from Youtube or Google

SELECT ENAME,SUBSTR(ENAME,1,2) || SUBSTR(ENAME,-1) FROM EMP;
SELECT SUBSTR(ENAME,1,2) || RIGHT(ENAME,1) FROM EMP;

SELECT LEFT(ENAME,2) || RIGHT(ENAME,1) FROM EMP;
SELECT CONCAT(SUBSTR(ENAME,1,2),SUBSTR(ENAME,-1)) FROM EMP;



18.ADD NEW COLUMN NAME WITH LNAME AND UPDATE 10th DEPT VALUES WITH YADAV ,20TH DEPT WITH GOWDA AND REMAINING WITH SINGH ?

ALTER TABLE EMP ADD LNAME VARCHAR ;
UPDATE EMP SET LNAME = (CASE WHEN DEPTNO = 10 THEN 'KUMAR' WHEN DEPTNO = 20 THEN 'GOWDA' ELSE 'RAO' END);


19.ADD NEW COLUMN NAME WITH FULLNAME AND UPDATE VALUES WITH FULLNAME WITH COMBINE FNAME AND LNAME WITH SPACE GAP ? (EXAMPLE : FNAME :SNOWFLAKE ,LNAME :CLOUD RESULT IN FULLNAME:SNOWFLAKE CLOUD)

ALTER TABLE EMP ADD FULLNAME VARCHAR;
UPDATE EMP SET FULLNAME = CONCAT(FNAME,' ',LNAME);


20.EXTRACT FNAME AND LNAME(AFTER SPACE IN FULLNAME) FROM FULLNAME COLUMN? (EXAMPLE : FULLNAME : SNOWFLAKE CLOUD ; RESULT IN FNAME = SNOWFLAKE)

SELECT FULLNAME,SUBSTRING(FULLNAME,1,CHARINDEX(' ', FULLNAME) -1) AS FNAME, SUBSTRING(FULLNAME, CHARINDEX (' ', FULLNAME) +1, LEN(FULLNAME)) AS LANEM FROM EMP;

21.ADD NEW COLUMN WITH EMAIL UPDATE DATA IN EMAIL WITH FULLNAME REPLACE SPACE WITH _ AND ADD @YAHOO.COM WHOSE SALARY LESS THAN 1500;@GMAIL.COM WHOSE SALARY BETWEEN 1500 TO 2000 AND @REDIFF.COM WHOSE SALARY MORE THAN 2000 ??
--SAMPLE DATA INPUT AND OUTPUT

FULLNAME SAL

BLAKE KUMAR 1000

JONES GOWDA 2500

RAJA RAO 2000


HINT::REPLACE(),CONCAT(),CASE / MULTIPLE UPDATE STATEMENT


EXPECTED OUTPUT:

EMAIL

BLAKE_KUMAR@YAHOO.COM

RAJA_RAO@GMAIL.COM

JONES_GOWDA@REDIFF.COM


ANS: ALTER TABLE EMP ADD EMAIL STRING;

METHOD 1:


UPDATE EMP SET EMAIL = CONCAT(REPLACE(FULLNAME,' ','_'),'@YAHOO.COM') WHERE SAL <1500;

UPDATE EMP SET EMAIL = CONCAT(REPLACE(FULLNAME,' ','_'),'@GMAIL.COM') WHERE SAL BETWEEN 1500 AND 2500;

UPDATE EMP SET EMAIL = CONCAT(REPLACE(FULLNAME,' ','_'),'@REDIFF.COM') WHERE SAL >2500;


METHOD 2:


UPDATE EMP SET EMAIL =(CASE WHEN SAL<1500 THEN CONCAT(REPLACE(FULLNAME,' ','_'),'@YAHOO.COM') WHEN SAL BETWEEN 1500 AND 2000 THEN CONCAT(REPLACE(FULLNAME,' ','_'),'@GMAIL.COM') WHEN SAL >2000 THEN CONCAT(REPLACE(FULLNAME,' ','_'),'@REDIFF.COM') END);