Scribd
Upload
3 views100 pages

SQL

The document provides an overview of applications, categorizing them into standalone, web, and client/server applications, along with their advantages and disadvantages. It also introduces SQL, its history, and its functionalities related to database management, including CRUD operations and data types. Additionally, it explains the concepts of DBMS, RDBMS, constraints, and SQL statements used for data manipulation.

Uploaded by

Dhanush Coc
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
3 views100 pages

SQL

The document provides an overview of applications, categorizing them into standalone, web, and client/server applications, along with their advantages and disadvantages. It also introduces SQL, its history, and its functionalities related to database management, including CRUD operations and data types. Additionally, it explains the concepts of DBMS, RDBMS, constraints, and SQL statements used for data manipulation.

Uploaded by

Dhanush Coc
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
INTRODUCTION 4PM What is an Application? " Isa list of programs which helps us to perform some a particular task/action " Example: WhatsApp MS office Web browser [ CHROME, MOZILLA] Facebook Instagram GTA Vice city Types of Applications: 1. Stand Alone Application. 2. Web Application. 3. Client / Server Application (Mobile Application). 1, STAND ALONE APPLICATIO! - Ell fo \| Lo Software installed in one computer and used by only one person, Examples Installing s/w of a Calculator, Adobe Photoshop, MS Office, AutoCAD, Paint etc... Advantages: Faster in access. Secured from Data hacking and virus. Disadvantages: © Single user access at a time. [New Section 1 Page 1 « Installation is required [Link] APPLICATIO! Dinga- Facebook Ding. Any Application which is opened through a browser is known as WEB APPLICATION. Examples: - Facebook , wiki , YouTube , Gmail , amazon , SkillRary ete. ... Server: It is nothing but a super computer where in all the applications are installed and can be accessed by anyone. [High Configuration] [Link] / SERVER APPLICATION: Facebook App - client app ‘Now Section | Page2 In Client Server application, unlike Standalone Application, part of application is installed on to the client system and the remaining part is installed on to the server machine. Examples: - Facebook, WhatsApp, Instagram, YouTube, Wiki, OLX, Flipkart ete..... Advantages: Easy to access and faster in access if the bandwidth is more Data security from data hacking and virus Data sharing is possible Maintenance is not so tough Multiple users can access the application. Disadvantages Installation is still required at client's place If the server goes down no one can access the application EXAMPLE: exoNTNO MiDDLEWARE aca os Banctwith FACEBOOK CLIENT APP ees a ‘Now Section | Page 3 INTRIDUCTION OF SQL 22July2021 08:02 PM SQL is standard computer language SQL stands for STRUCTURED QUERY LANGUAGE SQL is used to communicate / interact and manipulate the DATABASE WHAT CAN SQL DO......? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new tables in a database SQL can create views in a database HISTORY OF SOL 1) SQL WAS DESIGNED BY ‘RAYMOND BOYCE’ IN THE YEAR 1970. 2) FATHER OF SQL IS KNOWN AS RAYMOND BOYCE. 3) [Link] DESGINED RELATIONAL MODEL IN THE YEAR OF 1970 (SYSTEM.R). 4) E.F CODD IS CALLED BY CO-FATHER OF SQL. 5) IN OLDEN DAYS SQL IS CALLED BY SEQUEL . S>SIMPLE ESENGLISH QUE> QUERY L> LANGUAGE ONCE ANSI(AMERICAN NATIONAL STANDARD INSTITUTE) TOOK OVER AND THEY WAS CHAGNED THE NAME AS SQL. 6 New Section | Page 5 DATA: ” Data is a Raw fact which describe the property of an object Properties or Attributes object or entity Create a new account It’s quick and easy fee ne | Female © Male © Custom @ _— _— Tam creating my account for miyself ect Attributes . LAPTOP LAPTOP FirstName: Krishna Sur Name: Gowda ee eee Brand: Dell Brand: Dell rassword: xyz@ : : Password: xa RAM: gb RAM: gb Genders Male ‘ouch: no ‘ouch: yes HIEGHT: 10CM WATER BOTTLE |} ———> _ COLOUR: BULE CAPACITY: 500ML. DATA BASE "Data base is place or media which we can store the data in systematic and organized manner’ —_> oe TABASE wc} [New Sesion Page > The basic operations that can be performed on the database are > CREATE/ INSERT > READ/RETRIVE => > UPADTE/ MODIFY — > DROP/DELETE wemc > > "This operation are universally known as “CRUD” operation" Data base Management system (DBMS) DATA peet*t (QUERY LANGUAGE BR | B WF svionzanion FILE DATABASE SOFTWARE > DBMS is a software which is used to maintain and manage the database > Security and Authorization are the two imp Key feature provided by the DBMS. > We use Query Language to communicate or interact the DBMS > DBMS Stores the data in the form of Files Ls lle Ie A | | c Il» | RELETIONAL DATE BAS! .NGEMENT SYSTEM ( RDBMS) STRUCTERD save a secunry PANGUAGE BE cronzanss DATABASE’ SORTWARE TABLE ‘New Section 1 Page 7 _| — al AUIHUUZALIUN DATABASE: SOFTWARE TABLE > RDBMS atype of the DBMS which is used to store the data in the Form of {able > We can use to communicate or interact RDBMS by using SOL. (Structured Query Language) EXAMPLE: (gp A B c D E ASSINMENT: 1)DIFFERENCE BETWEEN THE DBMS AND RDBMS krish_sql_techie ‘New Seston | Page § og eM RELATIONAL MODEL > Relational Model was designed by £.F. CODD ¥ In Relational model we store the Data in the form of Rows and Columns Any DBMS which follows Relational model becomes on RDBMS DBMS RELATIONAL MODEL RDBMS OR Any DBMS which follows [Link] becomes on RDBMS, Table: ‘Table is logical organization of Data which consists of Rows and columns Column / Attribute / Field [he Columns: » Acolumns is also known as attributes OR field. > A column is used to represent property of all the entity. Ro ¥ Rowisalso known as Record or Tuples. > Row is used to represent all properties of a single entities. Cell: » Cellis the smallest unit of the table in which we store the data. > The interaction of rows and columns generate cells. Example : Emp (Entity ) oO - Eid EMP - Ename DD Eee ee - Salary — 1 SMITH = 1000 —?2 ALLEN 1500 —>3 CLARK 2000 RULES OF [Link] > The data entered into a cell must be ''single valued data" (atomic) Example : EID ENAME PHONE NO L SMITH 101 3 CLARK 103 EID ENAME PHONE NO ALTERNATE NO L SMITH 101 null 2 ALLEN 102 202 3 CLARK 103 > According to E.F. CODD we can store the data in multiple table, It necessary are can established the connection between the table with the help of “key attribute” > In RDBMS are store everything in the form of table including “META DATA” Example : Metadata : The details about a data is knows as Metadata. 1 3 ep ENAME Fu pata resolution : 400 x 600 ALLEN oN FHOTO CLARK [_] format: jpeg DATA MetaTable So Hiagename size Format Resolution Mypic 127 jpeg 400.x 600 Data enter into the table mus/ be validated = By assigning "DATATYPE" — By assigning "CONSTRAINTS" Datatype are mandatory whereas constrains are optional DATA TYPE: "DATATYPE is used to specify/ determine the type /kind of data that will be stored in a particular memory allocation." DATA TYPES In SOL 1, Char 2. Varchar/ Varchr2 3. Number 4. Date 5. Large object => Character large object (clob) => Binary large object (blob) Note: SQI Not case sensitive Language 1) CHAR: "In CHAR data type we can store characters such as” Example; 'A-Z' , ‘a-z’ , 0-9" and Special character ('$",'#,'%', °&") > Whenever we used char data type we must specify the size Syntax: CHAR (Size) Size: Numbers of characters Size: + Ttis used to specify number of characters that can be stored we can store a max of 2000 characters — character must always be with in single quote ('') > Character datatype follows "Fixed length memory allocation’ > Default size of char is one Example: CHAR(8) [Sma | 12s 4s K/R] I] s|u t USED MEMORY t 2) VARCHAR VARCHAR2: "In VARCHAR data type we can store characters such as" Example: 'A-Z! , ‘a-z’ , ‘0-9’ and Special character ('$', '#,'%', '&') > Whenever we used varchar data type we must specify the size Syntax: VARCHAR — ize: Numbers of characters — tis used to specify number of characters that can be stored — we can store a max of 2000 characters — character must always be with in single quote (" Ex: ‘ABC’ Raju’, ‘A? > VARChar datatype follows "variable length memory allocation" , default size of varchar is zero Example: VARCHAR(8) a2 os as K/R 1] si/H iv v s 7 8 { USED MEMORY t NoTI VARCHAR? is nothing but update version of varchar MAX size is $000 character ASSIGNMENT 1)DIFFRENACE BETWEEN CHAR AND VARCAHR varchar? [Link] ' This number datatype is used to store numeric value’ Number datatype can accept 2 arguments such as 0 Precision 0 Scale SYNTAX NUMBER(Precision, [scale © Precision 2 Precision is used to determine the number of digit used to store Integer value’ © The range of the precision is J to 38 © Scale © Scale is used to determine the number of digit used to store Decimal (float) val within the precision o The default value of scale is ‘zero! O The range of scale is -84 to 127 Examples : PRECISION SCALE [Link] The date datatype is used to store the date in specific format given by oracle 1)'DD-MON-YY' __'28-JUN-21" OR 2)'DD-MON-YYYY' '28-JUN-2021' [Link] OBJECT * CHARACTER LARGE OBJECT : (CLOB, Character large object (CLOB) is used to store huge amount of the character up-to AGB of size SYNTAX {CLOB | CHARACTER LARGE OBJECT } [(LENGTH [{KIMIG}])] * BINARY LARGE OBJECT : (BLOB, Binary large object (BLOB) is used to store the binary values of "IMAGES, MP3, MP4, PDE" etc. up-to 4GB of size SYNTAX {BLOB | BINARY LARGE OBJECT } [(LENGTH [{KIMIG}])] wbwee wv . FOREIGN KEY Daya CONSTRAINTS : Itis.a rule given to a column for validation . ‘Types of Constraints : - UNIQUE NOT NULL CHECK . PRIMARY KEY . FOREIGN KEY . . UNIQUE: "It is used to avoid duplicate values into the column". . NOT NULL : "it is used to avoid Null". . CHECK : "Ft is an extra validation with a condition If the condition is satisfied then the value is accepted else Rejected" EXAMPLE: CHECK(LENGTH(PH_NO))=10 CHECK (PERCENTAGE>60) |. PRIMARY KEY : "It is @ constraint which is used to identify a record Uniquely from the table" Characteristics of Primary key ; > We can have only | PK in a table > PK cannot accept duplicate / repeated values . > PK cannot accept Null & PK is always a combination of Unique and Not Null Constraint. > PK is not mandatory but highly recommended. “Iris used to establish a connection between the The tables" Characteristics of Foreign key We can have Multiple FK in a table FK can accept duplicate / repeated values . FK can accept Null FK is not a combination of Unique and Not Null Constraint. For an Attribute ( column ) to become a FK it is mandatory that it must be a PK in its own table . FK are present in child table but it actually belongs to parent table FK is also known as "REFRENTIAL INTEGRITY CONSTRAINT" VYVYY vy Examp! EMP "26-JUN-1998" PRADEEP "28-JAN-2001" 3214567890 CUSTOMER (GD cname|cno {EDD NAME SALARY Cid(fk)| Dnoifky 1 x 1001 Lila 10000 1 10 2 ¥ 2002 2 |B 20000 | 20 3 Zz Ld 3 |c __ 35000 20 4 |D _50000 2 10 Child /accepter DEPT DNAME|LOC| 10 DI Li 20 D2 L2 ASSIGNMENT : 1. Differentiate between Primary key and Foreign key . PRIMARY KEY FOREIGN KEY Ttis used to identify a records ‘tis used to establish a connection Uniquely from the table. Between the tables Tt cannot aceept Null It can accept Null Tt cannot accept duplicate values Ttean accept duplicate values Itis always a combination of Not Null {It is not a combination of and Unique constraint Not Null and Unique constraint ‘We can have only 1 PK ina table Wecan have Multiple FK in atable | NOT! Null Characteristics of Null: > Null doesn’t represent 0 or Space . > Any operations performed on a Null will result in Null itself > Null doesn’t Occupy any Memory > We cannot Equate Null . LL. ‘a keyword which is used to represent Nothing / Empty Cell. Ex: emp eid _/ename sal_|comm 1 a 100130 ens rl > 1b 1200 2eo=ulratsozbie) STATEMENTS OVERVIEW OF SOL STATEMENTS ; DATA DEFINITION LANGUAGE ( DDL ) DATA MANIPULATION LANGUAGE ( DML ) TRANSCATION CONTROL LANGUAGE (TCL ) DATA CONTROL LANGUAGE (DCL ) DATA QUERY LANGUAGE ( DQL ) obey DATA QUERY LANGUAGE (DOL): " DOL is used to retrieve/Read/fetch the data from the database " Ithas 4 statements I. SELECT 2. PROJECTION 3. SELECTION 4. JOIN SELECT ;"It is used to retrieve the dara from the table and display it. PROJECTION; "It is a process of ret Projection". > In projection all the records / values present in a particular columns are by default selected. ng the data by selecting only the columns is known as SELECTION ; a process of retrieving the data by selecting both the columns and rows is known as Selection " . OIN "It is a process of retrieving the data from Multiple tables simultaneously is known as Join". PROJECTION > “Itis a process of retrieving the data by selecting only the columns is known as Projection". > In projection all the records / values present in a particular columns are by default selected . SYNTAX: SELECT * / (DIST! FROM Table_Name Name/ Expression [ALIAS] [New Section 1 Page 19 SELECT * / [DISTINCT] Column_Name / Expression [ALIAS] FROM Table_Name ; ORDER OF EXECUTION 1. FROM Clause 2. SELECT Clause Example : .Write a query to display names of al the students SELECT SNAME FROM STUDENT; DATABSE TABLE OUTPUT OF FROM CLASUE OUTPUT OF SELECT CLAUSE STUDENT SAME SNAME BRANCH PER A ECE 60 8 CSE 75 c ME 50 © ECE 80 £ CSE 75 : CIVIL 95 FROM CLAUSE: » FROM Clause starts the execution > For FROM Clause we can pass Table Name as an argument > The job of FROM Clause is to go to the Database and search for the table and put the table under execution SELECT CLAUSE > SELECT Clause will execute afier the execution of FROM Clause ¥ For SELECT Clause we pass 3 arguments i, Asterisk(*) ii, Column_Name iii, Expression > The job of SELECT Clause is to go the table under execution and select the columns mentioned > SELECT Clause is responsible for preparing the result table NOTE [New Section 1 Page 20 > Asterisk(*) : > Semicolon : it means end of the query. Example > WAQTD student id and student names for all the students, > > > > EMP Table; WAQTD name and branch of all the students - WAQITD sname , sid , per , branch of all the students WAQTD details of all the students from students table means to select all the columns from the table . WAQTD NAME , BRANCH AND PERCENTAGE FOR ALL THE STUDENTS, EMPNO | ENAME | JOB HIREDATE [MGR | SAL_| COMM] DEPTNO 7369 [SMITH [CLERK | 17-0eC-30 | 7902 | 800 20. 7499 ALLEN | SALESMAN] 20-F88-81 | 7698 | 1600 300 | 30 7521 | WARD _| SALESMAN | 22-F¢8-81 | 7698 | 1250 500 | 30 7566 [sones | MANAGER | 02-Apr-81 | 7639 | 2975 20. 7658 | MARTIN] SALESMAN] 28:56°-61 | 7698 | 1250 1000 | 30 7698 | BLaxe | MANAGER | 01-May-81 | 7839 | 2850 30) 778 | CLARK | MANAGER | 09-1UN-a1 | 739 | 2450 70 778 | scort_| ANALYST _| 19-aR-87 | 7565 | 3000 20 7839 | KING _ | PRESIDENT | [Link].81 5000) 10 aaa | TURNER | SALESMAN 08-S€°-81 | 7698 | 1500[0 | 30 7a76_[ ADAMS | CLERK | 23-May-87 | 7788 | 1100 20. 7900 [JAMES | CLERK | 03.08C-8i | 7698 | 950 30) 7902 | FORD | ANALYST | 03-0ec-a1 | 7566 | 3000 20 79a [Mer | CLERK | 23-1aN-82 | 7782 | 1300 10 EXAMPLE OF EMP TABLE > WAQTD name salary and commission given to all the employees > WAQTD name of the employee along with their date of joining . DNAME Loc ACCOUNTING| NEW YORK, 20 RESEARCH __| DALLAS 30 SALES CHICAGO 40 OPERATIONS | BOSTON > WAQTD dname and location for all the depts . "New Section I Page 21 wEeY Sena WAQTD ALL THE DETAILS OF DEPT TABEL JUESTIONS ON iP D DEPT TABLI WRITE A QUERY TO DISPLAY ALL THE DETAILS FROM THE EMPLOYEE TABLE. WAQTD NAMES OF ALL THE EMPLOYEES, WAQTD NAME AND SALARY GIVEN TO ALL THE EMPLOYEES. WAQTD NAME AND COMMISSION GIVEN TO ALL THE EMPLOYEES. WAQTD EMPLOYEE ID AND DEPARTMENT NUMBER OF ALL THE EMPLOYEES IN EMP TABLE, WAQTD ENAME AND HIREDATE OF ALL THE EMPLOYEES WAQTD NAME AND DESIGNATION OF ALL THE EMPLPOYEES WAQTD NAME , JOB AND SALARY GIVEN ALL THE EMPLOYEES. WAQTD DNAMES PRESENT IN DEPARTMENT TABLE. WAQTD DNAME AND LOCATION PRESENT IN DEPT TABLE, DISTINCT Clause ""Itis used to remove the duplicate or repeated values from the Result table ". Distinct clause has to be used as the first argument to select clause. Distinct clause must be used before column name/ expression ‘We can use multiple columns as an argument to distinct clause, it will remove the combination of columns in which the records are duplicated SELECT DISTNICT SNAME,PER,BRANCH Example FROM STUDENT: Student SID | SNAME] BRANCH|PER SNAME]|PER |BRANCH 1A ECE |o0 A oo [ECE SNaME[PER | BRANCH A [eo [ece 2B csE__|75 B 75 |CSE 3 c ME. 50 5 75 __| CSE c 50 |ME ©1501 me + = — = D 80 |ECE c__|s0 [ese ; > [ao ece o£ cit [9s c 50_|CSE | es Tew E 95 [civ [New Section 1 Page 22, DAY 6 15May 2021 03:19 9M. EXPRESSION “Any statement which gives result is known as Expression ". Expression is a combination Operand and Operator . Qperand : These are the values that we pass . Operator : These are the Symbols which perform some Operation on The Operand . opeRAND Example: 5 * 10 otRaToR EMP EID |ENAME) SAL 1 A 100 2 B 200 3 Cc 100 % WAQTD name and salary given to the employees . > WAQTD name and annual salary of the employees . > WAQTD all the details of the employee along with annual salary > WAQTD name and salary with a hike of 20%. ® WAQTD name and salary of an employee with a deduction Of 10% JAS It is an alternate name given to a Column or an Expression In the result table". > We can assign alias name with or without using 'As' keyword > Alias names have to be a single string which is separated by An underscore or enclosed with double quotes . FORMAT :| ANNUAL_SALARY "ANNUAL SALARY" » WAQTD name and salary with a deduction 32% . SELECT ENAME,SAL,SAL-SAL*32/100 DEDUCTION FROM EMP; Now Section | Page 28 >» WAQTD annual salary for all the employees SELECT SAL*12 FROM EMP; ASSIGNMENT ON EXPRESSION & ALIAS WAQTD NAME OF THE EMPLOYEE ALONG WITH THEIR ANNUAL SALARY. WAQTD ENAME AND JOB FOR ALL THE EMPLOYEE WITH THEIR HALF TERM. SALARY. 3) WAQTD ALL THE DETAILS OF THE EMPLOYEES ALONG WITH AN ANNUALBONUS OF 2000. 4) WAQTD NAME SALARY AND SALARY WITH A HIKE OF 10%. 5) WAQTD NAME AND SALARY WITH DEDUCTION OF 25%. 6) WAQTD NAME AND SALARY WITH MONTHLY HIKE OF 50%. 7) WAQTD NAME AND ANNUAL SALARY WITH DEDUCTION OF 10%. 8) WAQTD TOTAL SALARY GIVEN TO EACH EMPLOYEE (SAL+COMM). 9) WAQTD DETAILS OF ALL THE EMPLOYEES ALONG WITH ANNUAL SALARY. 10) WAQTD NAME AND DESIGNATION ALONG WITH 100 PENALTY IN SALARY. COMMANDS ON Si ‘lus CLEAR SCREEN [ CL SCR ] : To clear the screen SET LINES 100 PAGES 100 : To set the dimensions of the output page EXIT / QUIT : To Close the Sofiware . When account is Locked !!! Log in as SYSTEM Password Oracle1234 ALTER USER SCOTT ACCOUNT UNLOCK ; ALTER USER SCOTT IDENTIFIED BY TIGER : s SELECT * FROM TAB ; > EMP > DEPT » SALGRADE » BONUS DESC TABEL_NAME-~--- DESCRIPTION THE TABLES. [Now Section Page 24 VVVVY DAY 7 15May2021 SELECTION: "It is a process of retrieving the data by selecting beth the columns and rows is known as Selection " SYNTAX SELECT * / [DISTINCT] Column_Name / Expression [ALIAS] FROM Table_Name WHERE ; ORDER OF EXECUTION 1, FROM. 2. WHERE [Link] WHERE Clause "Where clause is used to filter the records ". Where clause execute row by row Where clause execute after the execution of from clause. In Where clause we can write the filter_condition. The return type of condition in the form of Boolean (true or false). We can write multiple condition in where clause with the help of logical operator. EXAMPLE: WAQTD names of the employees working in dept 20 . 1. WAQTD names of the employees getting salary More than 300 . 2. WAQTD names and salary of the employees working in dept 10. 3. WAQTD alll the details of the employees whose salary is Less than 1000 rupees . 4. WAQTD name and hiredate of an employee hired on '09-JUN-198 L' "New Section 1 Po 5S. WAQTD details of the employee whose name is 'Miller* 6. WAQTD details of the employee hired after '01-JAN-1982' 7. WAQTD name sal and hiredate of the employees who were Hired before 1985 . SELECT ENAME,SAL,HIREDATE FROM EMP WHERE HIREDATE < '01-JAN-1985"; 8, WAQTD name sal and hiredate of the employees who were Hired after 1985. 9. WAQTD name of the employees who was hired on Valentine's day 2021 ASSIGNMENT ON WHERE Clause [Link] THE ANNUAL SALARY OF THE EMPLOYEE WHOS NAME IS SMITH [Link] NAME OF THE EMPLOYEES WORKING AS CLERK [Link] SALARY OF THE EMPLOYEES WHO ARE WORKING AS SALESMAN [Link] DETAILS OF THE EMP WHO EARNS MORE THAN 2000 §.WAQTD DETAILS OF THE EMP WHOS NAME IS JONES. [Link] DETAILS OF THE EMP WHO WAS HIRED AFTER 01-JAN-81 [Link] NAME AND SAL ALONG WITH HIS ANNUAL SALARY IF THE ANNUAL SALARY IS MORE THAN 12000 [Link] EMPNO OF THE EMPLOYEES WHO ARE WORKING IN DEPT 30 [Link] ENAME AND HIREDATE IF THEY ARE HIRED BEFORE 1981 [Link] DETAILS OF THE EMPLOYEES WORKING AS MANAGER LILWAQTD NAME AND SALARY GIVEN TO AN EMPLOYEE IF EMPLOYEE EARNS A COMMISSION OF RUPEES 1400 [Link] DETAILS OF EMPLOYEES HAVING COMMISSION MORE THAN SALARY [Link] EMPNO OF EMPLOYEES HIRED BEFORE THE YEAR 87 [Link] DETAILS OF EMPLOYEES WORKING AS AN ANALYST "Now Section I Page 28 DAY 8 SMay2021 03:19PM OPERATORS IN SOL 1) ARITHEMATIC OPERATORS :-(+,-,*,/) 2) CONCATENATION OPERATOR :- (II) 3) COMPARISION OPERATORS =: or >) 4) RELATIONAL OPERATOR :-(>,<,>=.<=) 5) LOGICAL OP: ( AND , OR , NOT ) 6) SPECIAL OPERATOR :- 1. IN NOT IN BETWEEN NOT BETWEEN Nov keN e NOT LIKE 7) SUBQUERY OPERATORS:- 1. ALL 2. ANY, 3. EXISTS 4. NOT EXISTS CONCATENATION Operator : " tis used to join the strings ". Symbol : i SYNTAX ‘STRING’ || ‘STRING 2' NOTE: We can join 'N’ number of strings by using a concatenation operator. Example: WAQTD ename the emp working as manager SELECT ENAME FROM EMP WHERE JOB='MANAGER'; ENAME| ALLEN New Section | Page 30 JONES SELECT 'HI' | ENAME FROM EMP WHERE JOB-'MANAGER'; voveoy v vVowy v v v LOGICAL OPERATORS AND OR NOT We use logical operators to write multiple conditions WAQTD name and deptno along with job for the employee working in dept 10 WAQTD name and deptno along with job for the employee working as manager in dept 10 WAQTD name , deptno , salary of the employee working in dep 20 and earning less than 3000 WAQTD name and salary of the employee if emp earns More than 1250 but less than 3000 WAQTD name and deptno of the employees if the EMP works in dept 10 or 20 WAQTD name and sal and deptno of the employees If emp gets more than 1250 but less than 4000 and works in dept 20 . WAQTD name , job , deptno of the employees working as a manager in dept 10 or 30. WAQTD name , deptno , job of the employees working in dept 10 or 20 or 30 asa cler WAQTD name , job and deptno of the employees working as clerk or manager in dept 10. WAQTD name , job , deptno , sal of the employees working as clerk or salesman in dept 10 or 30 and eaming more than 1800 WAQTD ENAME AND DEPTNO OF THE EMPLOYEES BY EXCLUDING THE EMPLOYEES OF DEPT 10 .61 20. ASSIGNMENT ON LOGICAL OPERATORS : . WAQTD DETAILS OF THE EMPLOYEES WORKING AS CLERK AND EARNING LESS New Section | Page 3 Rv sows 12. 13. 14, 15. 16. 17. 18. 19. 20. 21. THAN 1500 . WAQTD NAME AND HIREDATE OF THE EMPLOYEES WORKING AS MANAGER IN DEPT 30 . WAQTD DETAILS OF THE EMP ALONG WITH ANNUAL SALARY IF THEY ARE WORKING IN DEPT 30 AS SALESMAN AND THEIR ANNUAL SALARY HAS TO BE GREATER THAN 14000. . WAQTD ALL THE DETAILS OF THE EMP WORKING IN DEPT 30 OR AS ANALYST . WAQTD NAMES OF THE EMPMLOYEES WHOS . SALARY IS LESS THAN 1100 AND THEIR DESIGNATION IS CLERK . WAQTD NAME AND SAL , ANNUAL SAL AND DEPTNO IF DEPTNO IS. 20 EARNING MORE THAN 1100 AND ANNUAL SALARY EXCEEDS 12000, . WAQTD EMPNO AND NAMES OF THE EMPLOYEES WORKING AS MANAGER IN DEPT 20 . WAQTD DETAILS OF EMPLOYEES WORKING IN DEPT 20 OR 30. 10. ML. WAQTD DETAILS OF EMPLOYEES WORKING AS ANALYST IN DEPT 10 WAQTD DETAILS OF EMPLOYEE WORKING AS PRESIDENT WITH SALARY OF RUPEES 4000 11. WAQTD NAMES AND DEPTNO , JOB OF EMPS WORKING AS CLERK IN DEPT 10 OR 20 WAQTD DETAILS OF EMPLOYEES WORKING AS CLERK OR MANAGER IN DEPT 10. WAQTD NAMES OF EMPLOYEES WORKING IN DEPT 10 , 20 , 30, 40 WAQTD DETAILS OF EMPLOYEES WITH EMPNO. 7902,7839. WAQTD DETAILS OF EMPLOYEES WORKING AS MANAGER OR SALESMAN OR CLERK WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 AND BEFORE 87 WAQTD DETAILS OF EMPLOYEES EARNING MORE THAN 1250 BUT LESS THAN 3000 WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 INTO DEPT 10 OR 30. WAQTD NAMES OF EMPLOYEES ALONG WITH ANNUAL SALARY FOR THE EMPLOYEES WORKING AS MANAGER OR CLERK INTO DEPT 10 OR 30 20. WAQTD ALL THE DETAILS ALONG WITH ANNUAL SALARY IF SAL IS BETWEEN 1000 AND 4000 ANNUAL SALARY MORE THAN 15000. New Section | Page 32 039M SPECIAL OPERATORS : IN NOTIN BETWEEN NOT BETWEEN Is IS NOT LIKE NOT LIKE IN t/t isa multi-valued operator which can accept multiple values At the RS. Syntax: Column_Name / Exp IN (v1, v2,.. Vn) Example : > WAQTD name and deptno of the employees working in dept 10 or 30 SELECT ENAME,DEPTNO- FROM EMP WHERE DEPTNO 10 OR DEPTNO-30; OR SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO IN (10,30); > WAQTD name and job of the employee working as a clerk or manager Or salesman SELECT ENAME,JOB FROM EMP WHERE JOB IN (‘CLERK', ' MANAGER’, 'SALESMAN’); > WAQTD empno , ename and salary of the employees whose empno Is 7902 or 7839 and getting salary more than 2925. SELECT EMPNO,ENAME,.SAL FROM EMP WHERE EMPNO IN (7902,7839) AND SAL>2925: NOTIN: it is «multi-valued operator which can accept multiple values At the RHS It is to IN op instead of selecting it Rejects the values . Syntax: Column_Name/Exp NOT IN (v1 ,v2,..vn) Example > WAQTD name and deptno of all the employees except the emp Working in dept 10 or 40. SELECT ENAME,DEPTNO [Now Section | Page 34 FROM EMP: WHERE DEPTNO NOT IN(10.40); > WAQTD name , deptno and job of the employee working in dept 20 but not asa elerk or manager SELECT ENAME,[Link] FROM EMP WHERE DEPTNO=20 AND JOB NOTIN (‘CLERK' , 'MANAGER): BETWEEN :"It is used whenever we have range of values" [Start value and Stop/end Value ] ‘Syntax Column_Name BETWEEN Lower_Range AND Higher_Range — + Between Op works including the range > — We cannot interchange the range !!. Example WAQTD name and salary of the employees if the emp is earning Salary in the range 1000 to 3000 SELECT ENAME,SAL FROM EMP WHERE SAL BETWEEN 1000 AND 3000; WAQTD name and deptno of the employees working in dept 10 And hired during 2019 (the entire year of 2019) SELECT ENAME,DEPTNO FROM EMP WHERE DEPTNO IN 10 AND HIREDATE BETWEEN '01-JAN-2019' AND '31-DEC-2019'; WAQTD name, sal and hiredate of the employees hired during 2017 into dept 20 with a salary greater than 2000 => NOT BETWEEN : It is Opposite of Between Syntax: Column_Name NOT BETWEEN Lower AND Higher_Range Example > WAQTD name and salary of the employees if the emp is not earning Salary in the range 1000 to » 3000. SELECT ENAME,SAL FROM EMP, [New Seton | Pag WHERE SAL NOT BETWEEN 1000 AND 3000; > WAQTD name and deptno of the employees working in dept 10 And not hired during 2019 , > WAQTD name , sal and hiredate of the employees who were not hired during 2017 into dept 20 > with a salary greater than 2000 SELECT ENAME SAL HIREDATE FROM EMP, WHERE HIREDATE NOT BETWEEN ‘O1-JAN-2017" AND °31-DEC-2017' AND DEPTNO IN 20 AND SAL>2000; = IS: "Iris used to compare only NULL" Syntax: Column_Name IS NULL: Example: EMP EID _ENAME|SAL | COMM I A__|1000| 100 Ble le B c 200 D_ | 2000 > WAQTD name of the employee who is not getting salary . > WAQTD name of the emp who doesn’t get commission . > WAQTD name , sal and comm of the emp if the emp doesn’t earn both(SAL AND COMM). = ISNOT : "Iris used to compare the values with NOT NULL ". Syntax; Column_Name IS NOT NULL ; Example > WAQTD name of the employee who is getting salary > WAQTD name of the emp who gets commission . > WAQTD name , sal and comm of the emp if the emp doesn’t earn commission but gets salary ASSIGNMENT QUESTIONS WAQTD NAMES AND DEPTNO , JOB OF EMPS WORKING AS CLERK IN DEPT 10 OR 20 WAQTD DETAILS OF EMPLOYEES WORKING AS CLERK OR MANAGER IN DEPT 10 AND EARNING SAL MORE THAN 1250 WAQTD NAMES OF EMPLOYEES WORKING IN DEPT 10.20 , 30,40. AS A CLERK WAQTD DETAILS OF EMPLOYEES WITH EMPNO 7902,7839 IN DEPT 10 OR 30 WAQTD DETAILS OF EMPLOYEES WORKING AS MANAGER OR SALESMAN OR CLERK IN DEPT 40 => WAQTD NAMES OF EMPLOYEES HIRED AFTER 81 AND BEFORE 87 AS A PRESIDENT uy uuu Now Section IP = WAQTD DETAILS OF EMPLOYEES EARNING MORE THAN 1250 BUT LESS THAN 3000 WAQTD NAMES OF EMPLOYEES HIRED AFTER 2020 INTO DEPT 10 OR 30 WAQTD NAMES OF EMPLOYEES WITH SALARY FOR THE EMPLOYEES WORKING AS MANAGER OR CLERK INTO DEPT 10 OR 30 WAQTD ALL THE DETAILS IDSAL IS BETWEEN 1000 AND 4000 IN DEPT 10 OR 20 AND WORKING AS A MANAGER OR ANALYST ‘Now Sesion 1 Page 37 DAY 10 18May osm LIKE: "It iy used for Pattern Matching ". To achieve pattern matching we use special characters . > Percentile (%) » Underscore (_) Percentile (%) : itis a special character which is used to match any number of character, any number of time / number of character. Underscore (_): itis a special character which is used to match exactly once but any character. Syntax}|Column_Name/ exp LIKE ‘pattern’ ; Example WAQTD details of an employee whose name is SMITH . SELECT * FROM EMP WHERE ENAME ‘MITH": WAQTD details of the employee whose name starts with 'S'. SELECT * FROM EMP WHERE ENAME LIKE 'S%’; WAQTD details of the employee whose name ends with 'S'. SELECT * FROM EMP WHERE ENAME LIKE '"%S'; WAQTD names of the employees who have character 'S' in their names, SELECT ENAME FROM EMP WHERE ENAME LIKE '%S%'; NOTE: "A' _| SELECT LAST CHAR, 'A%! | SELECT FIRST CHAR "%@A%'_| SELECT ANYWHERE CHAR WAQTD names that starts with ‘I’ and ends with'S! SELECT ENAME FROM EMP WHERE ENAME LIKE 'J%S': WAQTD names of the employee if the emp has char 'A'as his second character . SELECT ENAME FROM EMP WHERE ENAEM LIKE '_A%' WAQTD names of the employee if the emp has char'A’ as his Third character WAQTD names of the employee if the emp has char AV as his second character and 'S'is last character WAQTD names of the employee if the emp has char 'V" present atleast 2 times SELECT ENAME FROM EMP WHERE ENAME LIKE "V%V%': WAQTD names of the employee if the emp name starts with 'A’ and ends with'A'. > WAQTD names of the employee if the emp's salary’s last 2 digit is 50 rupees SELECT ENAME FROM EMP WHERE SAL LIKE "%50' > WAQTD names of the employees hired in November SELECT ENAME FROM EMP WHERE HIREADTE LIKE "*NOV%'; > WAQTD names and sal of employee if the emp annual salary’s last 3 digit is 200 rupees SELECT ENAME ,SAL FROM EMP WHERE SAL*12 LIKE '%200' NOTLIKE: Opposite of Like Syntax {Column_Namelexp NOT LIKE patter > WAQTD names of the employees NOT hired in November SELECT ENAME FROM EMP WHERE HIREDATE NOT LIKE "%NOVY%'; > WAQTD names of the employee if the emp has not a char ‘A’ as his Third character ASSIGNMENT ON SEPCIAL OPERATORS : 1) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL 2) LIST ALL THE EMPLOYEES WHO DON'T HAVE A REPORTING MANAGER 3) LIST ALL THE SALESMEN IN DEPT 30 4) LIST ALL THE SALESMEN IN DEPT NUMBER 30 AND HAVING SALARY GREATER THAN 1500 5) LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH ‘S* OR *A* 6) LIST ALL THE EMPLOYEES EXCEPT THOSE WHO ARE WORKING IN DEPT 10 & 20. 7) LIST THE EMPLOYEES WHOSE NAME DOES NOT START WITH °S’ 8) LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10 9) LIST ALL THE EMPLOYEES WHOSE COMMISSION IS NULL AND WORKING AS CLERK 10) LIST ALL THE EMPLOYEES WHO DON’T HAVE A REPORTING MANAGER IN DEPTNO 10 OR 30 11) LIST ALL THE SALESMEN IN DEPT 30 WITH SAL MORE THAN 2450 12) LIST ALL THE ANALYST IN DEPT NUMBER 20 AND HAVING SALARY GREATER THAN 2500 13) LIST ALL THE EMPLOYEES WHOSE NAME STARTS WITH *M* OR ‘J 14) LIST ALL THE EMPLOYEES WITH ANNUAL SALARY EXCEPT THOSE WHO ARE WORKING IN DEPT 30 15) LIST THE EMPLOYEES WHOSE NAME DOES NOT END WITH ‘ES’ OR ‘R’ 16) LIST ALL THE EMPLOYEES WHO ARE HAVING REPORTING MANAGERS IN DEPT 10 ALONG. WITH 10% HIKE IN SALARY DISPLAY ALL THE EMPLOYEE WHO ARE ‘SALESMAN’S HAVING ‘E’ AS THE LAST BUT ONE CHARACTER IN ENAME BUT SALARY HAVING EXACTLY 4 CHARACTER 18) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED AFTER YEAR 81 19) DISPLAY ALL THE EMPLOYEE WHO ARE JOINED IN FEB 20) LIST THE EMPLOYEES WHO ARE NOT WORKING AS MANAGERS AND CLERKS IN DEPT 10 AND 20 WITH A SALARY IN THE RANGE OF 1000 TO 3000. 17 DAY 11 8 oet1PM FUNCTIONS "Functions Are a block of code or list of instructions which are used to perform a specific task ". There are 3 main components of a function 1) Funetion_Name 2) Number_of arguments ( no of inputs ) 3) Return type TWO TYPES| fuseR DEFINED BUILT IN FUNCTION FUNCTION [SINGLE ROW MULTI ROW FUNCTION FUNCTION SRFO, MRFO) oureur | input wwout 1 > : 2 z > ° > oureur 2 N —> ss EX:LENGTHU EX: MAX ENAME [SAL ALLEN | 100 JONES _|200 [Link] | 390 [Link] SHLECT MAX(SAL) SELECT LENGTH(ENAMI a FROM EMP: \ ——— function | [arguanent Tenumn nipe + Single row function: 1. SRF execute ROW BY ROW vyvvy vvvy 2. SRF takes one input process and executes it to generate an output and takes next input and so on 3. IF We pass'n’ number input to the single row function, it returns 'n’ number of output Multi Row Functions; MRF execute GROUP BY GROUP It takes all the inputs at one shot and then executes and provides A single output . If we pass 'n' number of inputs to a MRF() it returns 'I' Output Listof MRF () ‘MAX{ ) : itis used to obtain the maximum value present in the column. MIN () + itis used to obtain the minimum value present in the column SUM. it is used to obtain the summation of values present in the column AVG(): itis used to obtain the average of values present in the column COUNT( ): it is used to obtain the number of values present in the column. NOTE: Multi row functions can accept only one argument , je, a Colummn_Name oran Expression MRF (Column_Name / Exp ) Along with a MRF() we are not supposed to use any other Column_Name in the select clause MRF()) ignore the Null We cannot use a MRF( ) in where clause . COUNT( is the only MRF which can accept * as an Argument . (count(*)) Examples: WAQTD maximum salary given to a manager WAQTD Total salary given to dept 10 WAQTD number of employees earning more than 1500 in dept 20 WAQTD number of employee having 'E’ in their names WAQTD minimum salary given to the employees working as clerk in Dept 10 or 20. WAOTD number of employees hired afier 1982 and before 1985 into Dept 10 or 30 WAQTD number of employees getting commission WAQTD maximum salary given to employees if the emp has character'S' in the name and works as a Manager in dept 10 with as salary of more than 1800 WAQTD number of employees working in dept 10 or 30 and getting commission without the salary WAQTD maximum salary given to a manager working in dept 20 and also his comm must be greater than his salary ASSIGNEMENT ON MRF() = WAQTD NUMBER OF EMPLOYEES GETTING SALARY LESS THAN 3000 IN DEPTNO 10 = WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES WORKING AS CLERK = WAQTD AVERAGE SALARY NEEDED TO PAY ALL EMPLOYEES = WAQTD NUMBER OF EMPLOYEES HAVING 'A' AS THEIR FIRST CHARACTER = WAQTD NUMBER OF EMPLOYEES WORKING AS CLERK OR, MANAGER = WAQTD TOTAL SALARY NEEDED TO PAY EMPLOYEES HIRED IN FEB = WAQTD NUMBER OF EMPLOYEES REPORTING TO 7839 (MGR) = WAQTD NUMBER OF EMPLOYEES GETTING COMISSION IN DEPTNO 30 = WAQTD AVG SAL, TOTAL SAL , NUMBER OF EMPS AND MAXIMUM SALARY GIVEN TO EMPLOYEES WORKING AS PERSIDENT => WAQTD NUMBER OF EMPLOYEES HAVING 'A' IN THEIR NAMES = WAQTD NUMBER OF EMPS AND TOTAL SALARY NEEDED TO PAY THE EMPLOYEES WHO: HAVE 2 CONSICUTIVE L's IN THEIR NAMES, => WAQTD NUMBER OF DEPARTMENTS PRESENT IN EMPLOYEE TABLE = WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER '7' IN THEIR NAMES = WAQTD NUMBER OF EMPLOYEES HAVING'S' IN THEIR NAMES . = WAQTD TOTAL SALARY GIVEN TO EMPLOYEES WORKING AS CLERK IN DEPT 30 = WAQTD MAXIMUM SALARY GIVEN TO THE EMPLOYEES WORKING AS ANALYST = WAQTD NUMBER OF DISTINCT SALARIES PRESENT IN EMPLOYEE TABLE, = WAQTD NUMBER OF JOBS PRESENT IN EMPLOYEE TABLE => WATQD AVG SALARY GIVEN TO THE CLERK = WAQTD MINIMUM SALARY GIVEN TO THE EMPLOYEES WHO WORK IN DEPT 10 AS MANAGER OR A CLERK DIFFRENACE BETWEEN SRF AND MRF Single Row Funetion (SRF) Multi Row Function (MRF) itis also called server function Itis also called aggregate/group function Execute row by row Execute group by group We can pass 'n’ number of inputs, it returns We can pass “n’ number of inputs, it returns ‘n’ number of outputs ‘single’ number of outputs used in where clause/statement/ keyword Not Used in where clause/statement/ keyword Along with the SRF can use anyother column Along with the MRF cannot use any other column It not ignores the null Itignores the null DAY 12 GROUP & FILTERING ‘GROUPING:GROUP-BY Clause Group by clause is used to group the records. NOTE Group By clause executes row by row After the execution of Group By clause we get Groups ‘The Column_Name or expression used for grouping can be used In sleet clause 1 3. Therefore any clause that executes after group by must execute Group By Group 4 5 Group By clause can be used without using Where clause SYNTAX; SELECT geoup_by_expression / group_function FROM table_name [WHERE ] GROUP BY column_namelexpression ORDER OF EXECUTION; 1. FROM 2. WHERE (fused) [ROW-BY-ROW] 3. GROUP BY [ROW-BY-ROW] 4. SELECT [GROUP-BY-GROUP] EME E1D| ENAME] SAL| DEPTNO| 1] a [lo] 20 2 |B [2070 3) [30) % 4] D [10010 s |e | 20) 0 e | A | 400) 30 7] C300 30 es] F [203 Example WAQTD number of employees working in each dept. SELECT COUNT(*), DEPTNO FROM EMP GROUP BY DEPTNO: OUTPUT OF GROUP BY CLAUSE wo OUTPUT OF FROM CLAUSE 2 [a [no [io ‘EID ENAME| SAL | DEPTNO) se fuo Tee 1| a | wol 20 | — <2 pee 2] 8 [200/10 3 | c_| 30) se p|-—_f2 sofa sl} oe bo) 10 — 300| 30 = count) 00 10 == soot Io le ee [20 400 | 30 5 500 | 20 200 | 30 mlo|>|m|olo 8 Questions: » WAQTD number of employees working in each dept except the Employee working as analyst > WAQTD maximum salary given to each job > WAQTD number of employees working in each job ifthe employees Have character in their names WAQTD number of employees getting commission in each dept ASSIGNMENT QUESTIONS ON GROUP BY WAQTD NUMBEROF EMPLOYEES WORKING IN EACH DEPARTEMENT EXCEPT PRESIDENT. WAQTD TOTAL SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH JOB. WAQTD NUMBER OF EMPLOYEES WORKING AS MANAGER IN EACH DEPARTMENT WAQTD AVG SALARY NEEDED TO PAY ALL THE EMPLOYEES IN EACH DEPARTMENT EXCLUDING THE EMPLOYEES OF DEPTNO 20. WAQTD NUMBER OF EMPLOYEES HAVING CHARACTER(A' IN THEIR NAMES IN EACH JOB WAQTD NUMBER OF EMPLOYEES AND AVG SALARY NEEDED TO PAY THE EMPLOYEES WHO SALARY IN GREATER THAN 2000 IN EACH DEPT. WAQTD TOTAL SALARY NEEDED TO PAY AND NUMBER OF SALESMANS IN EACH. DEPT, 8, WAQTD NUMBER OF EMPLOYEES WITH THEIR MAXIMUM SALARIES IN EACH JOB. 9, WAQTD MAXIMUM SALARIES GIVEN TO AN EMPLOYEE WORKING IN EACH DEPT. 10. WAQTD NUMBER OF TIMES THE SALARIES PRESENT IN EMPLOYEE TABLE EILTERING : HAVING Clause " Having Clause is used to Filter the Group" ‘NOTE: 1. Having clause execute group by group 2. Having clause always use after group by clause. 3. In Having clause we write group filter condition 44. Having clause cannot we used without the group by clause 5. In Having clause we can use multi row function( MRF) as condition. SYNTA: SELECT group_by_expression/ group_function FROM table_name [WHERE ] GROUP BY column_nameexpression HAVING ] [GROUP BY column_name/expression] [HAVING ] ORDER BY Column_name/expresston [ASC)DESC ORDER OF EXECUTION: 1. FROM 2 WHERE(fused) — [ROW-BY-ROW| 3. GROUPBY fused) [ROW-BY-ROW] 4. HAVING (if wed) [GROUP-BY-GROUP] 38 - 6 ORDER BY [ROW-BY-ROW] 300 50. SELECT SAL FROM EMP ORDER BY SAL [ASC]; SAL 50 120 300 SELECT SAL FROM EMP. ORDER BY SAL DESC SAL 300) 120 50 DAY 13 SUB-QUERY: juery written inside omother query isknown As sub query Working Principle —— = Let us conser two queries Outsr Query and Inner Query a. Inner Query exccutes first and produces an Outpt Bb. The Output of Inner Query is given’ fed as an Input to Outer Query ‘¢, The Outer Query generates the Result 1d. Therefore we can state thatthe Outer Query is dependent on Taner Query’ and this isthe Exceution Principle of Sub Query. Why / When Do we use SUB OUERY Cased ‘Whenever we have Unknowns present in the Question, We use sub query to find The Unknown values Eample: EME 4ip__| |DEPLNO| | ALLEN] 1000] 20 2 | BLAKE! 2000] 10 3 CLARK) 3000] 30 “| MILLER 1500/ Wo [swimmer 9500] 10. L. WAQTD names of the employees eaming more than 2800. 2. WAQTD names ofthe employees eaming ess than MILLER 3. WAQTD name und deptno ofthe emplayes working in the same Dept as SMITH 4 WAQTD name and hiredate ofthe employees ifthe employee Was hired after JONES 5. WAQTD al the details ofthe employee working inthe same Designation as KING 6 z ® 8 WAQTD name. sal depino ofthe employees if the employees Ear more than 2000 and work in the same deptas JAMES WAQTD all the details ofthe employees working inthe Same designation as MILLER and eaming more than 1500. WAQTD details ofthe employees saming mors than SMITH But less than KING. WAQTD name, sal and deptno ofthe employees ifthe employee Is eaming commission in dept 20 and earning salary more than Scott WAQTD name snd hirdate ofthe employees those name ends with'S' and hired after James 10. WAQTD names of the employees working in the same dept as JAMES ancl eaming salary more than ADAMS and working inthe same job role as MILLER and hired after MARTIN LL. WAQTD all the details ofthe employees working as salesman in the dept 20 andl caring commission more than Smith and hired after KING 12. WAQTD number of employees earning more than SMITH and less thin MARTIN 18. WAQTD Ename and SAL forall the employees earning more than JONES, oni «Inthe Inner Query / Sub Query we cannot select more than One column. +The corresponding columns need not be sme , but the datatypes of those has to be same ASSIGNMENT ON CASE. 1) WAQTD NAME OF THE EMPLOYEES FARNING MORE THAN ADAMS 2) WAQTD NAME AND SALARY OF THE EMPLOYEES EARNING LESS THAN KING 3) WAQTD NAME AND DEPTNO OF THE EMPLOYEES IF THEY ARE WORKING IN THE SAME DEPT AS JONES 4) WAQTD NAME AND JOB OF ALL THE EMPLOYEES WORKING IN THE SAME DESIGNATION AS JAMES 5) WAQTD EMPNO AND ENAME ALONG WITH ANNUAL SALARY OF ALL THEEMPLOYEES IF THEIR ANNUAL 6) SALARY IS GREATER THAN WARDS ANNUAL SALARY. 3) WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY ARE HIRED BEFORE SCOTT. 8) WAQTD NAME AND HIREDATE OF THE EMPLOYEES IF THEY ARE HIRED AFTER THE PRESIDENT 9) WAQTD NAME AND SAL OF THE EMPLOYEE IF THEY ARE EARNING SAL LESS THAN THE EMPLOYEE WHOS EMPNO 15 7839 lo) WAQTD ALL THE DETAILS OF THE EMPLOYFES IF THE EMPLOVEES ARE HIRED BEFORE MILLER 11) WAQTD ENAME AND EMPNO OF THE EMPLOYEES IF EMPLOYEES ARE EARNING MORE THAN ALLEN 12) WAQTD ENAME AND SALARY OF ALL THE EMPLOYEES WHO ARE EARNING MORE THAN MILLER BUT LESS THAN ALLEN 13) WAQTD ALL THE DETAILS OF THE EMPLOYEES WORKING IN DEPT 20 AND WORKING IN THE SAME DESIGNATION AS SMITH 14) WAQTD ALL THE DETAILS OF THE FMPLOYFES WORKING AS MANAGER IN THE SAME DEPT AS TURNER 15) WAQTD NAME AND HIREDATE OF THE EMPLOYEES HIRED AFTER 1980 AND BEFORE KIN Io) WAQTD NAME AND SAL ALONG WITH ANNUAL SAL FOR ALL EMPLOYEES WHOS SAL IS LESS THAN BLAKE AND MORE THAN 3500 13) WAQTD ALL THE DETAILS OF EMPLOYEES WHO FARN MORE THAN SCOTT BUT LESS THAN KING 1s) WAQTD NAME OF THE EMPLOYEES WHOS NAME STARTS WITH 'A' AND WORKS IN THE SAME DEPT AS BLAKE 19) WAQTD NAMF AND COMM IF EMPLOYEES EARN COMISSION AND WORK IN THE SAME DESIGNATION AS SMITH. 20) WAQTD DETAILS OF ALL THE EMPLOYEES WORKING AS CLERK IN THE SAME DEPT AS TURNER. 21) WAQTD ENAME, SAL AND DESIGNATION OF THE EMPLOYEES 22) WHOS ANNUAL SALARY IS MORE THAN SMITH AND LESS THAN KING. DAY 14 SUB-QUERY CASE- Whenever the data to be selected and the condition to be executed are present in different tables we use Sub Query Example EMP elD [ENAME|SAL[DEPTNO- ALLEN | 1000] 21 BLAKE | 2000] 10 CLARK | 3000| 30 MILLER| 1500| 10 ADAMS 2500 | 20 WAQTD depino of the employee whose name is Miller WAQTD dname ofthe employee whose name is Miller SELECT DNAME FROM DEPT WHERE DEPTNO= (SELECT DEPTNO FROM EMP. WHERE ENAME='MILLER)) WAQTD Location of ADAMS SELECT LOC FROM DEPT WHERE DEPTNO =(SELECT DEPTNO FROM EMP WHERE ENAME~'ADAMS)), WAQTD names of the employees working in Location L2. WAQTD number of employees working in dept D3 WAQTD Ename, sal ofall the employee eaming more than Scott and working in dept 20 WAQTD all the details of the employee working as a Manager In the dept Accounting WAQTD al the details of the employee working in the same designation as Miller and works in location New York WAQTD number of employees working asa clerk in the same deptno as SMITH and eaming more than KING hited after MARTIN in the location BOSTON WAQTD maximum salary given toa person working in DALLAS EXAMPLI CUSTOMER peepee GAD | CNAME|CNO [PID {eb DISCOUNT 1 [SMITH |12345/101 101 Iphone 11|51000 0 2 JONES | 12349) 102, 102 Iphone 12}74000_| 1000 3 [ALLEN | 12346) 103 103 [iPad [40000 [100 WATQD the name of the product that smith purchased SELECT PNAME FROM PRODUCT WHERE PID =(SELECT PID FROM CUSTOMER WHERE CNAMI Now Sete Page 12. WAQTD the names ofthe customers who have purchased Iphone 12. ASSIGNMENT ON CASE 2: LWAQTD DNAME OF THE EMPLOYEES WHOS NAME IS SMITH [Link] DNAME AND LOC OF THE EMPLOYEE WHOS ENAME IS KING [Link] LOC OF THE EMP WHOS EMPLOYEE NUMBER IS 7902 [Link] DNAME AND LOC ALONG WITH DEPTNO OF THE EMPLOYEE WHOS NAME ENDS WITH R’ [Link] DNAME OF THE EMPLOYEE WHOS DESIGNATION IS PRESIDENT 6, WAQTD NAMES OF THE EMPLOYEES WORKING IN ACCOUNTING DEP ARTMENT TWAQTD ENAME AND SALARIES OF THE EMPLOYEES WHO ARE WORKING IN THE LOCATION CHICAGO, SAWAQTD DETAILS OF THE EMPLOYEES WORKING IN SALES [Link] DETAILS OF THE EMP ALONG WITH ANNUAL SALARY IF EMPLOYEES. ARE WORKING IN NEW YORK 10. WAQTD NAMES OF EMPLOYEES WORKING IN OPERATIONS DEPARTMENT ASSIGNMENT ON CASE 1&2 LWAQTD NAMES OF THE EMPLOYEES EARNING MORE THAN SCOTT IN ACCOUNTING DEPT [Link] DETAILS OF THE EMPLOYEES WORKING AS MANAGER IN THE LOCATION CHICAGO [Link] NAME AND SAL OF THE EMPLOYEES EARNING MORE THAN KING IN THE DEPT ACCOUNTING [Link] DETAILS OF THE EMPLOYEES WORKING AS SALESMAN INTHE DEPARTEMENT SALES. [Link] NAME, SAL, JOB, HIREDATE OF THE EMPLOYEES WORKING IN OPERATIONS DEPARTMENT AND HIRED BEFORE KING [Link] ALL THE EMPLOYEES WHOSE DEPARTMET NAMES ENDING ' [Link] DNAME OF THE EMPLOYEES WHOS NAMES HAS CHARACTER A’ INIT SWAQTD DNAME AND LOC OF THE EMPLOYEES WHOS SALARY IS RUPEES 800 [Link] DNAME OF THE EMPLOYEES WHO EARN COMISSION [Link] LOC OF THE EMPLOYEES IF THEY EARN COMISSION IN DEPT 40 DAY 15 Stuly 2021 12:24PM TYPES OF SUB - QUERY : 1) SINGLE ROW SUB QUERY 2) MULTI ROW SUB QUERY EXAMPLE: EMP DEPT EID ENAME SAL |DEPTNO DEPTNO DNAME|LOC 1 ALLEN | 1000 20 10 DI Ll 2 | BLAKE 2000 10 20 D2 L2 3 CLARK 3000 30 30 D3 L3 4 MILLER 1500 10 5 SMITH | 2500 10 ** SINGLE ROW SUB QUERY: > If the sub query returns exactly | record / value we call it as Single Row Sub Query. > [fit rewurns only 1 value then we can use the normal operators Or the Special Operators to compare the values EXAMPLE » WAQTD DNAME OF ALLEN SELECT DNAME FROM DEPT WHERE DEPTNO =(SELECT DEPTNO, FROM EMP WHERE ENAME='ALLEN)); “+ MULTI ROW SUB QUERY: > Ifthe sub query returns more than] record / value we call it as Multi Row Sub Query. > If it returns more than I value then we cannot use the normal operators We have to use only Special Operators to compare the values. Now Section 1 Page 54 EXAMPLE » .WAQTD DNAMES OF ALLEN OR SMITH. SELECT DNAME FROM DEPT WHERE DEPTNO. (SELECT DEPTNO. FROM EMP WHERE ENAME IN(‘ALLEN' , 'SMITH’); Here ,since the sub query returns 2 records / values so we cannot use 's' op. we have to use 'IN' OP SELECT DNAME FROM DEPT WHERE DEPTNO JIN (SELECT DEPTNO Note : FROM EMP WHERE ENAME IN(‘ALLEN' , ‘SMITH'); 1, WAQTD Ename and salary of the employees earning more than Employees of dept 10. EMP. EID ENAME|SAL_DEPTNO: 1 _ALLEN | 1000 20 2 BLAKE | 2000 10 3. CLARK |3000 30 4 MILLER| 1500 10 5 SMITH |2500 10 Now Section 1 Page 55 = ORIN © ORNOTIN >(SINGLE VALUES} >ALL(MULTI) 5 SMITH 2500 10 SELECT ENAME ,SAL FROM EMP WHERE SAL| >|(SELECT SAL FROM EMP 2000 AO VOL. VR >ALL(MULTI) 1500>1500,3000 1500 2500 WHERE DEPTNO= 10); HERE we cannot use > symbol to compare multiple values we cannot use IN or NOT IN OP as well because itis used for = and <> symbol therefore we have to use SUB QUREY OP to comparing relational operators such as (>,<,<=,>=) Sub Query Operators : 1. ALL 2. ANY 3. EXIST 4, NOT EXIST ALL: "It is special Op used along with a relational Op (>, <, > = , <=) to compare the values present at the RHS". ALL Op returns true if all the values at the RHS have satisfied the condition . EAXMPLE 7. WAQTD Ename and salary of the employees earning more than Employees of dept 10. SELECT ENAME,SAL (CLARK, 3000) FROM EMP WHERE SAI[> ALL] (SELECT SAL 1000 FROM EMP WHERE DEPTNO. New Section 1 Pa 1000 2000 3000 1500 2500 AN} "It is special Op used along with a relational Op (>.<, > FROM EMP WHERE DEPTNO =I10- 1000 > 2000 FALSE 1000 1500 FALSE 1000>2500 FALSE 2000> 2000 F 2000>1500T 200022500 F REJECTED. 2500 REJECTED 30002000 3000> 1500 T SELECTED 30002500, T 150022000 F 1500>1500 1500>2500F REJECTED 250022000 2500>1500 2500> 2500 _F REJECTED values present at the RHS ". , <=) to compare the ANY Op returns true if one of the values at the RHS have satisfied the condition. Example ‘Now Section 1 Page 57 > WAQTD ENAME,SAL WHO ARE EARNING SAL MORE THAN ANY ONE OF THE EMPLOYEE IN THE DEPTNO 10 SELECT ENAME,SAL (BLAKE CLARK SMITH — 2000,3000,2500) FROM EMP WHERE SAL|>ANY| (SELECT SAL FROM EMP {#22 WHERE DEPTR@=I 0); 2000 100022000 F 1o00>1500 FE 1o00>2500F REJECTED 1500>2000F 1500>1500_F 1500> 2500 F REJECTED 1. WAQTD name of the employee if the employee earns less than ALL The employees working as salesman . SELECT ENAME FROM EMP WHERE SAL

You might also like