dbms chapter 2.pdf

 Tuples:- A single row in a table , which contains a single record for that relation is called a tuple.  Attributes:- The headers of the tables are called as attributes , for each attribute there is a set of permitted values, called the domain of that attribute. Ex. For the attribute branch_name...

 Tuples:- A single row in a table , which contains a single record for that relation is called a tuple.  Attributes:- The headers of the tables are called as attributes , for each attribute there is a set of permitted values, called the domain of that attribute. Ex. For the attribute branch_name , the domain is the set of all branch_name.  Relation:- -Relation is something used to refer to a table in a relational model. -In relational data model, relations are saved in the format of table.  RDBMS:- -It is an advanced technique to store and access the data. It is a database based on the relational model.  Codd’s Rule for Relational DBMS:- -E. F. Codd was a computer scientist who invented the relational model for DBMS based on relational model. -Codd proposed 12 rules popularly known as Codd’s 13 rules to test DBMS’s concept against his relational model. -Codd’s rule actualy defined what quality a DBMS requires in order to become a RDBMS.  Codd’s 12 rule:- 1]Rule 0:- This rule states that for a system to qualify as an RDBMS, it must be able to manage database entirely through the relational capabilities. 2]Rule1:-(information rule) All information (including metadata ) is to be represented as stored data in cells of tables. The rows and columns have to be strictly unordered. 3]Rule2:-(Guaranteed Access) Each unique piece of data(atomic data) should be accessible by: Table Name+Primary key(Row)+ Attribute(Column) 4]Rule3:-(Systematic treatment of NULL) -NULL has several meanings, it can mean missing data , not applicable or no value. -It should be handled consistently. -Also primary key must not be NULL, ever. 5]Rule4:- (Active online catalog) -Database directory (catalog) is the structure description of the complete database and it must be stored online. -The catalog must be governed by same rules as rest of the database. -The same query language should be used on catalog as used to query database. 6]Rule5:-(Powerful and well structured language) -One well structure language must be there to provide all manners of access to the data stored in the database. Ex. SQL etc. -If the database allows access to the data without the use of this language then that is a violation. 7]Rule6:-(View updation rule) -All the view that are theoretically updatable should be updatable by the system. 8]Rule7:-(Relational level operation) -There must be Insert, Update operations at each level of relations. -Set operation like Union, Intersection and Minus should also be supported. 9]Rule8:-(Physical data independence) -The physical storage of data should not matter to the system. -It says that , some file supporting table is renamed or moved from one disk to another, it should not effect the application. 10]Rule 9:-(Logical data independence) -If there is a change in the logical structure (table structure ) of the database the user view of data should not change. -If a table is split into two tables, a new view should give result as the join of the two tables. -This rule is most difficult to satisfy. 11]Rule10:- (Integrity independence) -The database should be able to enforce its own integrity rather than using other programs. key and check constraints, trigger etc. should be stored in data dictionary. - 12]Rule11:- (Distribution independent) -A database should work properly regardless of its distribution across a network. -Even if a database is geographically distributed , with data stored in pieces the end user should get an impression that it is stored at the same place. 13]Rule12:- (Non subversion Rule) -If a relational system supports a low level language , that low-level language cannot be used to subvert or bypass the integrity rule or constraints expressed in the higher level relational language.  Structured Query Language:- -SQL is a standard database language which is used to create , maintain and retrieve the relational database.  Categoriesof SQL command:- 1.DDL(Data Definition Language) 2.DML(Data Manipulation Language) 3.DCL(Data Control Language) 4.TCL(Transaction Control Language)  Data Definition Language(DDL):- -DDL consists of the SQL commands that can be used to define the database schema. -It simply deals with descriptions of the database schema and is used to create and modify the structure of db objects in db. 1.CREATE:- -It is used to create the database or its objects (like table, index,function,views,store procedure and triggers). i]create database ii]create table i]Create database:-  Syntax:-create database database_name; -database_name:name of the database  Ex.create database my_database; Ii]Create table:-  Syntax:-create table table_name (column1 data_type(size), column2 data_type(size), column3 data_type(size), -------------------------------, --------------------- ---------); -Table_name:name of the table. -Column1:name of the first column. -Data_type: type of data we want to store in the particular column. Ex. int for integer -Size: size of data.  EX.Create table Students(ROLL_NO int (3), NAME varchar(20),SUBJECT varchar(20)); 2.DROP, TRUNCATE:-  DROP is used to delete a whole database or just a table. The DROP statement destroys the objects like an existing database, table, index or view.  Syntax:-drop object object_name;  EX.drop table table_name; drop table students;  TRUNCATE:- -TRUNCATE statement in DDL is used to mark the extents of a table for deallocation (empty for reuse). -The result of this operation quickly removes all data from a table, typically by passing a number of integrity enforcing mechanism. Syntax:-truncate table table_name; -Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure. -Table or database deleting using DROP statement cannot be rolled back, so it must be used wisely. 3]ALTER(ADD,DROP,MODIFY):- -Alter table is used to add, delete (drop) or modify columns I the existing table. -It also used to add and drop various constraints on the existing table. -ALTER TABLE –ADD:- -ADD is used to add columns into the existing table. Sometimes we may requires to add additional information in that case we do not require to create the whole database again, ADD comes to our rescue.  Syntax:-alter table table_name add (columnname 1 datatype, columnname 2 datatype, ------------------, - - - - - - - - - - - - - - - - - -, Columnname n datattype); -ALTER TABLE-DROP:- -Drop column is used to drop column in a table. Deleting the unwanted columns from the table.  Syntax:- alter table table_name drop column column_name; -ALTER TABLE-MODIFY:- -It is used to modify the existing columns in table. Multiple columns can also be modified at once.  Syntax:- alter table table_name modify column_name column_datatype;  Example:- Student table Roll_no Name 1 ABC 2 PQR 3 XYZ -To add 2 columns Age and Course to table Student alter table Student add(Age integer, Course varchar(40)); Roll_no Name Age Course 1 ABC 2 PQR 3 XYZ  Example:- Student table Roll_no Name Age Course 1 ABC 2 PQR 3 XYZ -Modify column Course in table Student alter table Student modify Course varchar(20); -Drop column Course in table Student alter table Student drop column Course; Roll_no Name Age 1 ABC 2 PQR 3 XYZ 4.Comment:- -It is used to add comments to the data dictionary. -Comments can be written in the following three formats 1.Single line comments 2.Multi line comments 3.In line comments. 1]Single line comments:- -Comments starting and ending in a single line are considered as a single line comments. -Line starting with ‘-’ is a comment and will not be executed.  Ex:- - - single line comment select * from Student; 2]Multiline comment:- -Comment starting in one line and ending in different line are considered as multiline comments. -Line starting with ‘’ is encountered. EX:- select * from Student; 3]Inline Comment:- -Inline comments are extension of multiline comments. Comments can be started in between the statement and are enclosed in between ‘’ Ex:-select * from Employee; 5. Rename:- -Sometime we want to rename our table to give it a more relevant name. For this purpose we can use ALTER TABLE to rename the name of table. -Syntax:- alter table table_name rename to new_table_name; -Columns can be also be given new name with the use of ALTER TABLE. -Syntax:- alter table table_name rename column old_name to new_name; 6]DESC TABLE:- -It describes either the columns in a table or the current (and default) values for the table. Ex.:-create table emp(id integer not null primary key, fname varchar(50), lname varchar(50), location varchar(100)); -Describe the columns in the table. desc emp; +- - - - - - - - - - + - - - - - - - - - - - - - - - + | name | type | | ID | NUMBER(38,0) | | FNAME | VARCHAR(50) | | LNAME | VARCHAR(50) | | LOCATION | VARCHAR(100) | + - - - - - - - - - -+ - - - - - - - - - - - - - - - +  Data Manipulation Language:- -The SQL command that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements. 1]SELECT:- -Select is a most commonly used statement in SQL. -The select statement in SQL is used to retrieve or fetch data from db. -We can fetch either the entire table or according to some specified rule. -The data returned is stored in a result table. -This result table is also called as result set. -With the SELECT clause of a SELECT command statement, we specify the columns that we want to be displayed in the query result and optinally which column heading we prefer to see above the result table.  Syntax:- select column1, column2,column3 from table_name; -To fetch the entire table or all the fields in the table select * from table_name; 2]INSERT :- -The INSERT INTO statement of SQL is used to insert a new row in a table. -There are two ways of using INSERT INTO statement for inserting rows. i)Only value:- -First method is to specify only the value of data to be inserted without the column names. Syntax:-INSERT INTO table_name VALUES(value1, value2, value3,…………); i)Column name and values both:- -In the second method we will specify both the columns which we want fill and their corresponding values as shown below Syntax:- insert into table_name (column1,column2,……….) values(value1,value2,…….); Queries:- Method1:- insert into student values(‘5’,’Harsh’,’West Bengal’,’8765789998’,’19’); Method2:-insert into student(Roll_no., Name, Age) values(‘6’,’Pratik’,’19’); -Notice that the columns for which the values are not provided are filled by NULL which is the default values for those columns. 3]UPDATE statement:- -The UPDATE statement in SQL is used to update the data of an existing table in database. -We can update single columns as well as multiple columns using UPDATE statement as per our requirement. Syntax:-UPDATE table_name SET column1=value1, column2=value2,………….. WHERE condition; -In the above query the SET statement is used to set new values to the particular column and the WHERE clause is used to select the rows for which the columns are needed to be updated. -If we have not used the WHERE clause the columns in all the rows will be updated. -So WHERE clause is used to choose particular rows. 1]Updating single column:- -Update the column name and set the value to ‘PRATIK’ in all the rows where Age is 20.  Query:-UPDATE student SET NAME = ‘PRATIK’ WHERE Age=20; 2]Updating multiple columns:- -Update the column name to ‘PRATIK’ and Address to ‘SIKKIM’ where Roll_no is 1.  Query:-UPDATE student SET name=‘PRATIK’ Address = ‘SIKKIM’ WHERE Roll_no =1; 3]Omitting WHERE clause:- -If we omit the WHERE clause from the update query then all of the rows will get updated. Query:- UPDATE student SET name=‘PRATIK’; 3]DELETE statement:- -The DELETE statement in SQL is used to delete existing records from a table. -We can delete a single record or multiple records depending on the condition we specify in the WHERE clause. Syntax:- delete from table_name where condition; -We can delete single as well as multiple records depending on the condition we provide in WHERE clause. -If we omit the WHERE clause then all of the records will be deleted and the table will be empty. i]Deleting single record:- -Delete the rows where name=‘Ram’ this will delete only the first row. Query:-Delete from student where name=‘Ram’; ii]Deleting multiple records:- Delete the rows from the table student where Age is 20. This will delete rows where age is 20. Query:-delete from student where Age=’20’; iii]Delete all of the records:- There are two queries to do this as shown below: Query1:-delete from student; Query2:-delete * from student;  -TCL Commands in SQL:- -Transaction control language commands are used to manage transactions in the database. -These are used to manage the changes made to the data in a table by DML statements. -It also allows statements to be grouped together into logical transactions. 1]COMMIT command:- -COMMIT command is used to permanently save any transaction into the database. -When we use any DML command like INSERT , UPDATE or DELETE the changes made by these commands are not permanent , until the current session is closed , the changes made by these commands can be rolled back. -To avoid that we use the COMMIT command to mark the changes as permanent. Syntax:- COMMIT; 2]ROLLBACK command:- -This command restores the database to last committed state. -It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction. -If we have used the UPDATE command to make some changes into the database and realize that those changes were not required then we can use the ROLLBACK command to rollback those changes if they were not committed using the COMMIT command. Syntax:- ROLLBACK TO savepoint_name; 3]SAVEPOINT command:- -SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point wherever required. Syntax:- SAVEPOINT savepoint_name; -In short using this command we can name the different states of our data in any table and then rollback to that state using the ROLLBACK command wherever required. Example:-Using TCL commands Table name:-Class id name 1 Abhi 2 Adam 4 Alex -Use some SQL queries on the above table and see the results. INSERT INTO class VALUES (5, ‘Rahil’); COMMIT; UPDATE class SET name=‘Abhijit’ WHERE ID=‘5’; SAVEPOINT A; INSERT INTO class VALUES (6, ‘Chris’); SAVEPOINT B; INSERT INTO class VALUES (7, ‘BRAVO’); SAVEPOINT C; SELECT * FROM class; -The resultant table will look like id name 1 Abhi 2 Adam 4 Alex 5 Abhijit 6 Chris 7 Bravo -Now lets use the ROLLBACK command to roll back the state of data to the SAVEPOINT B. ROLLBACK TO B; SELECT * FROM class; -Now our class table will look like- id name 1 Abhi 2 Adam 4 Alex 5 Abhijit 6 Chris -Now lets again use the ROLLBACK command to roll back the state of the to the SAVEPOINT A ROLLBACK TO A; SELECT * FROM class; id name 1 Abhi 2 Adam 4 Alex 5 Abhijit -DCL commands:- -Data control language is used to control privileges in database. -To perform any operation in the database such as for creating tables , sequences or views , a user needs privileges. Privileges are of two types: -System:-This includes permissions for creating session, table etc and all types of other system privileges. -Object:-This includes permissions for any command or query to perform any operation on the database tables. -Only database Admin or owners of the database object can provide/remove privileges on a database object. -GRANT command:- -This command is used to provide access or privilege on the database objects to the users. Syntax:-GRANT privilege_name ON object_name TO {User_name|PUBLIC|Role_name} [WITH GRANT OPTION]; Privilege_name:-It is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT. The list of object privileges is ALTER, DELETE, INDEX,INSERT, SELECT, UPDATE. Object_name:-Is the name of an db object like TABLE, VIEW, STORED PROCEDURES, and SEQUENCES. -User_name:-It is the name of the user to whom an access right is being granted. -PUBLIC:-It is used to grant access right to all users. -ROLES:-Are a set of privileges grouped together. -WITH GRANT OPTION:-Allow user to grant access rights to other users. -EXAMPLE:- GRANT SELECT ON employee TO user1; -This command grants a SELECT permission on employee table to user1. You should use the WITH GRANT option carefully because for example if you grant select privilege on employee table to user1 using the WITH GRANT option then user1 will can GRANT SELECT privilege on employee table to another user, such as user2 etc. -Later ,if you REVOKE the SELECT privilege on employee from user1 still user2 will have SELECT privilege on employee table. -REVOKE command:- -The REVOKE command removes user access rights or privileges to the database objects. Syntax:- REVOKE privilege_name ON object_name FROM {user_name|PUBLIC|Role_name}; Example:- REVOKE SELECT ON employee FROM user1; This command will REVOKE a SELECT privilege on employee table from user1. When you REVOKE SELECT privilege on a table from a user , the user will not be able to SELECT data from that table anymore. However, if the user has received SELECT privilege on that table from more than one users, he/she can SELECT from that table until everyone who granted the permission revokes it. You can not REVOKE privileges if they were not initially granted by you. -Functions in SQL:- Aggregate functions:- -Sometimes for decision making we need summarize data from table like average, sum etc. -SQL provides various aggregate functions which can summarize data of given table. -The function operates on the table data produces a single output. -Such queries are generally used for producing reports and summary form in an applications.  Types of aggregate function:- 1.COUNT() 2.SUM() 3.AVG() 4.MIN() 5.MAX() 6.FIRST() 7.LAST()  Exam_Marks Sid SName Marks 1 Mahesh 90 2 Suhas 80 3 Jayendra 89 4 Sachin 99 5 Vishal 88 6 Payal 90 1]COUNT():- -This function is used to calculate number of rows(or records) in a table selected by query. -COUNT returns the number of rows in the table when the column value is not NULL. -Column in the query must be numeric. Example:- Find total number of students in the Exam_Marks table. Query:- SELECT COUNT(Sid) as count FROM Exam_Marks; COUNT 6 2]SUM():- -This function is used to calculate sum of column values in a table selected by query. -Column in the query must be numeric. -Value of the sum must be within the range of that data type. Example:-Find the total marks scored by all students Query:-select sum(Marks) as sum from Exam_Marks; SUM 536 3]AVG():- -This function is used to calculate average of all non NULL column values in a table selected by query. -This function first calculates sum of column and then divide by total number of not null rows. -AVG returns the average of all the values in the specified column. -Column in the query must be numeric. Example:-Find average marks of students. Query:- select avg(Marks) as AVG from Exam_Marks; AVG 89.33 4]MIN():- -This function is used to find minimum value out of column values in a table selected by query. -Column in the query need not be numeric data type. Example:-Find minimum marks scored by students. Query:- select min(Marks) as Min from Exam_Marks; MIN 80 5]MAX():- -This function is used to find maximum value out of column values in a table selected by query. -Column in the query need not be numeric data type. Example:-Find maximum marks scored by students. Query:- select max(Marks) as Max from Exam_Marks; MAX 99 6]FIRST():--This function is used to find first column values in a table selected by query. Example:- Query:- select first(Marks) as FIRST from Exam_Marks; FIRST 90 7]LAST():- -This function is used to find last column values in a table selected by query. Example:- Query:- select last(Marks) as LAST from Exam_Marks; LAST 90 String functions:- 1]ASCII():- -This function returns the number code that represents the specific character. Example:- select ASCII(CustomerName) As NumCode from Customers; 2]CHAR():- -This function returns the character based on the number code. Syntax:-CHAR(number_code); Example:-select CHAR(65) As NumCode; 3]CHARINDEX():- -This function returns the location of a substring in a string. Syntax:- CHARINDEX(substring, string , start_pos); Example:-Search for “t” in string “Customer” and return position. Query:-select charindex(‘t’,’Customer’) As Matchposition; Matchposition 4 4]C0NCAT():- -This function is used to concatenate two or more strings together.. Syntax:-CONCAT(string1, string2 ,- - - - - - ,string n); Example:-. select concat(‘Second’,’Year’) ; O/P:-SecondYear 5]CONCAT with + :- -The + operator allows you to concatenate two or more strings together. -Syntax:-string1+string2+- - - - - - +string n; Example:- select ‘Second’ + ‘Year’; For more STRING functions visit site https://www.w3schools.com/sql/func_mysql_ascii.asp Date and time functions:-  DATE() Function:-  The date() is used to get the date from given date/datetime.  Syntax:- select date('expression');  Example:- Select date('2018-09-24 10:51'); date('2018-09-24 10:51') 2018-09-24  The adddate() function:-  The adddate() function is used to get the date in which some time/date intervals are added.  Syntax:- select adddate(date,interval value unit);  OR select adddate(date,days);  Example:- Select adddate('2018-09-24 ‘, interval 2 second); adddate('2018-09-24 ‘, interval 2 second) 2018-09-24 00:00:02 Date and time functions:-  The curdate() function:-  It is used to get the current date.  Syntax:- select curdate();  Example:- Select curdate(); Curdate() 2020-09-03  DATE_FORMAT() Function  It is used to get the date in specified format.  Syntax:- select date_format(date, format_mask);  Example:-select date_format(‘2018-09-24’, ’%M %D %Y’); date_format(‘2018-09-24’, ’%M %D %Y’); September 25th 2018 Date and time functions:-  DAY() Function:-  It is used to get the day from the given date.  Syntax:- select day(‘date_value’);  Example:- Select day(‘2018-09-25’); Day(‘2018-09-25’ 25  DAYNAME() Function  IIt is used to get the day from the given date.  Syntax:- select dayname(‘date’);  Example:-select dayname(‘2018-09-24’); dayname(‘2018-09-24’) Tuesday Date and time functions:-  month() Function:-  It is used to get the month from the given date.  Syntax:- select month(‘date_value’);  Example:- Select month(‘2018-09-25’); month(‘2018-09-25’) 09  MONTHNAME() Function  IIt is used to get the month from the given date.  Syntax:- select monthname(‘date_value’);  Example:-Select monthname(‘2018-09-25’); monthname(‘2018-09-24’) September Date and time functions:-  TIME() Function:-  It is used to get the time from given time/datetime.  Syntax:- select time('expression');  Example:- Select time('2018-09-24 10:51:00'); time('2018-09-24 10:51') 10:51:00  The addtime() function:-  The adddata() function is used to get the time/datetime in which some time intervals are added.  Syntax:- select addtime(start_value,time);  Example:- Select addtime(''2018-09-22', '11.0000023'); addtime(''2018-09-22', '11.0000023') 00:20:29:000002 Date and time functions:-  The curtime() function:-  It is used to get the current time.  Syntax:- select curtime();  Example:- Select curtime(); Curtime() 10:20:09  TIME_FORMAT() Function  It is used to get the date in specified format.  Syntax:-select time_format(time, format_mask);  Example:-select time_format(’12:56:12’, ’%H %i %S’); time_format(’12:56:12’, ’%H %i %S’); 12 56 12 Clauses in SQL:- 1]WHERE clause:- -It is used to extract only those records that fulfils a specified condition. -The where clause specifies the rows to be retrieved. -Since there is no where clause, all rows are retrieve by default. -Syntax:-SELECT column_name(s) FROM table_name WHERE condition; 2]HAVING clause:- -It is similar to the WHERE clause. -The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. -The difference between WHERE clause and HAVING clause is in the way the query is processed. -In the where clause, the search condition on the row is performing before rows are grouped. -In HAVING clause the groups are formed first and the search condition is applied to the group. -The WHERE clause places condition on the selected columns , whereas the HAVING clause places conditions on group created by the GROUP BY clause. -Syntax:-SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s); Worker table F_NAME STATUS GENDER BIRTHDATE Ashwini Regular F 11/01/70 Rahul Summer M 01/12/72 Ajay Regular M 05/03/69 Smita Regular F 23/09/67 Example:- SELECT * FROM Worker GROUP BY status , Gender HAVING Gender=‘F’; F_NAME STATUS GENDER BIRTHDATE Ashwini Regular F 11/01/70 Smita Regular F 23/09/67 Example:- SELECT * FROM Worker WHERE Birthdate < 11/01/70 GROUP BY status,Gender HAVING Gender=‘M’; F_NAME STATUS GENDER BIRTHDATE Ajay Regular M 05/03/69 3]GROUP BY Clause:- -A GROUP BY clause arranges your data rows into a group according to the columns you specify. -The group by clause is used in collaboration with the select statement to arrange identical data into groups. -When GROUP BY clause is used, each item in the SELECT list must be single-valued per group. -When WHERE clause is used with GROUP BY the WHERE clause is applied first , then groups are formed from the remaining rows that satisfy the search condition. Syntax:-SELECT col1,col2,...... FROM table_name WHERE condition GROUP BY col1,col2,……… ; Example:- SELECT * FROM Worker GROUP BY status; F_NAME STATUS GENDER BIRTHDATE Ashwini Regular F 11/01/70 Ajay Regular M 05/03/69 Smita Regular F 23/09/67 Rahul Summer M 01/12/72 Example:- SELECT * FROM Worker GROUP BY status,Gender; F_NAME STATUS GENDER BIRTHDATE Ashwini Regular F 11/01/70 Smita Regular F 23/09/67 Ajay Regular M 05/03/69 Rahul Summer M 01/12/72 4]ORDER BY Clause:- -ORDER BY clause is similar to the GROUP BY clause. -The ORDER BY clause enables you to sort your data in either ascending or descending order. Ascending order bydefault. -The ORDER BY clause consists of a list of column identifiers that the result is to be sorted on , separated by columns -A column identifier may be either a column name or a column number. Syntax:-SELECT col1,col2,...... FROM table_name [WHERE condition] ORDER BY col1,col2,……… ; Example:- SELECT * FROM Worker ORDER BY F_NAME; F_NAME STATUS GENDER BIRTHDATE Ajay Regular M 05/03/69 Ashwini Regular F 11/01/70 Rahul Summer M 01/12/72 Smita Regular F 23/09/67  Nested query in SQL:- -In nested queries, a query is written inside a query. -The result of inner query is used in execution of outer query. -The proper term for this nested SELECT statement is a subquery. -A nested SELECT is a query within a query, i.e. when you have a SELECT statement within the main SELECT. -SQL subqueries are a powerful tool. They allow us to perform tasks more efficiently by having only one query instead of several. -When using nested queries, keep these considerations in mind: -Subqueries can return single values or tables (with one or many rows and columns). -You can include a subquery: In the WHERE clause, to filter data. In the FROM clause, to specify a new table. In the SELECT clause, to specify a certain column. In the HAVING clause, as a group selector. -Subqueries should always be enclosed in parentheses(). -you can put a nested SELECT within the WHERE clause with comparison operators or the IN, NOT IN, ANY, or ALL operators.  The IN operator checks if a certain value is in the table returned by the subquery.  The NOT IN operator filters out the rows corresponding to the values not present in that table returned by a subquery.  The ANY operator is used with comparison operators to evaluate if any of the values returned by the subquery satisfy the condition.  The ALL operator is also used with comparison operators to evaluate if all values returned by the subquery satisfy the condition.  id name class_id GPA Student table 1 Jack Black 3 3.45 2 Daniel White 1 3.15 3 Kathrine Star 1 3.85 4 Helen Bright 2 3.10 5 Steve May 2 2.40 Classes table id grade teacher_id no_of_students 1 10 3 21 2 11 4 25 3 12 1 28  Teacher table id name subject class_id monthly_salary 1 Elisabeth Grey History 3 2,500 2 Robert Sun Literature [NULL] 2,000 3 John Churchill English 1 2,350 4 Sara Parker Math 2 3,000 -Let’s say you want to find all students that have above- average GPAs. However, you don’t know the average GPA score. Certainly, you can use a query to find out: SELECT AVG(GPA) FROM students; -You’ll get a number (3.19) that you can use to solve the initial task – showing all information for students with a GPA above this average:  Query:- SELECT * FROM students WHERE GPA > 3.19; -But can you solve this task in one step? You can with a nested query. Here is how it looks:  SELECT * FROM students WHERE GPA > (SELECT AVG(GPA) FROM students); - Our subquery here returns a single value (i.e. a table with a single column and a single row). This is important for the comparison operator to work. With the average GPA score returned by the inner query, the outer query can select the students who satisfy our filter condition (i.e. a GPA score above average). -And the result is: id name class_id GPA 1 Jack Black 3 3.45 3 Kathrine Star 1 3.85 -Let’s see how the IN operator works. In this example, you’ll calculate the average number of students in classes where the teacher teaches History or English:  SELECTAVG(no_of_students) FROM Classes WHERE teacher_id IN (SELECT id FROM Teachers WHERE subject = 'English' OR subject = 'History'); -Key Concepts:- 1]KEY:- -A key is a single or combination of multiple fields in a table. It is used to fetch/retrieve data from table also used to create relationship among different database tables or views. 2]SUPER KEY:- -It is a set of one or more than one keys that can be used to identify a record uniquely in a table. -or it is a set of one or more attributes that taken collectively allow us to identify uniquely an entity in the entity set. -Super key Example:-The customer_id attribute of the entity set customer is sufficient to distinguish one customer entity from another. Thus customer_id is a super key. -Similarly the combination of customer_name and customer_id is a super key for the entity set customer. -The customer_name attribute of customer is not a super key because several people might have the same name. 3]Candidate key:- -A candidate key is a set of one or more fields / columns that can identify a record uniquely in a table. -There may be multiple candidate keys in one table. -Each candidate key can work as primary key. Example:- -A combination of customer_name and customer_street is sufficient to distinguish among members of the customer entity set. Then both{customer_id} and {customer_name, customer_street} are candidate kays. -Although the attribute customer_id and customer_name together can distinguish customer entities, their combination does not form a candidate key , since the attribute customer_id alone is a candidate key. 4]Primary key:- -Primary key is a set of one or more fields of a table that uniquely identify a record in a database table. -It can not accept null/duplicate values. -Only one candidate key can be primary key. 5]Foreign key:- -Foreign key is a field in database table that is primary key in another table. -It can accept multiple null, duplicate values. -JOINS:- -SQL join is used to fetch data from two or more tables which is joined to appear as single set of data. It is used for combining column from two or more tables by using values common to both tables. Types of join are: 1]INNER JOIN OR EQUI JOIN 2]NON EQUI JOIN 3]OUTER JOIN 4]SELF JOIN 1]INNER JOIN OR EQUI JOIN:- -This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the SQL query. Syntax:- SELECT column_name_list FROM table_name1 INNER JOIN table_name2 WHERE table_name1.column_name= tsble_name2.column_name; Class table Class_info ID NAME ID ADDRESS 1 Abhi 1 DELHI 2 Adam 2 MUMBAI 3 Alex 3 CHENNAI 4 Anu -query:- Select * from class INNER JOIN class_info where class.id=class_info.id; ID NAME ID ADDRESS 1 Abhi 1 DELHI 2 Adam 2 MUMBAI 3 Alex 3 CHENNAI 2]NON EQUI JOIN:- -This join uses comparison operator instead of the equal sign like >,=,