LearnPick Navigation
Close

Sql Vidyasagar

Published in: PL/SQL
902 views
  • J V

    • Secundrabad
    • 11 Years of Experience
    • Qualification: MCA
    • Teaches: Physics, Mathematics, Chemistry, All Subjects, IT,...
  • Contact this tutor

sql vidyasagar

  • 1
    UNIT-3 - SQL SQL stands for Structured Query Language SQL lets you access and manipulate databases SQL is an ANSI (American National Standards Institute) standard What Can SOL 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 databases • SQL can create new tables in a database • SQL can create stored procedures in a database • SQL can create views in a database • SQL can set permissions on tables, procedures, and views Using SQL in Your Web Site To build a web site that shows some data from a database, you will need the following: • An RDBMS database program (i.e. MS Access, SQL Server, MySQL) • A server-side scripting language, like PHP or ASP • SQL • HTML/ CSS RDBMS RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows. Database Tables A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with data. SQL Statements Most of the actions you need to perform on a database are done with SQL statements. The following SQL statement will select all the records in the "Persons" table: SELECT * FROM PERSONS; Keep in Mind That... SQL is not case sensitive Semicolon after SQL Statements: o Some database systems require a semicolon at the end of each SQL statement. o Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
  • 2
    Explain the role of SQL in database development? (&/Or) Explain the advantages of 1. structured relational language (SQL)? Explain the SQL environment? (short) 2. 3 EXPLAIN DDL9 DML9 DCL COMMANDS? 4 EXPLAIN BRIEFL Y ABOUT THE OPERA TORS USED IN SQL? 5 WRITE BRIEF DESCRIPTION ON CLA USES USED IN SQL? (short) Grouping data from tables (This answer is a small part of 6th Answer — Groupby & Having 6. Clause) z WRITE SHOR T NOTES ON AGGREGA TE (GROUP) FUNCTIONS? 8 WRITE SHOR T NOTES ON CONSTRAINTS IN SQL? Explain Sub Queries/Nested Queries? 9. 10. Correlated nested queries/Correlated Sub Queries? 11. Write short notes on Data Schema (short) 12. Write short notes on Dual Table & Null values (short) 13. Write short notes on Views? (short) 14. Explain Client-Server database systems?
  • 3
    xplain the role of SQL in database development? &/Or Explain the advantages of structure elational SQL stands for Structured Query Language. SQL used to write queries that store, alter & retrieves data and metadata of a relational database. It is a non-procedural language. SQL is an ANSI (American National Standards Institute) standard This is a unified language i.e., common for many databases. It is a 4th generation language (4GL) in which programmer concentrates on what rather than how. SQL commands can be classified into 3 types. 1. DDL 2. DML 3. DCL Role of SQL: To specify the syntax and semantics of SQL data definition & manipulation languages To define the data structures & basic operations for designing, accessing, maintaining, controlling and protecting an SQL database. To establish portability of database definition and application modules between compatible DBMSs To specify both minimal and complete standards To provide an initial standard that will be enhanced later to include specifications for handling referential integrity, transaction management and user defined functions. Benefits of SQL: Reduced training costs: Training in an organization can concentrate on one language, A large team of IS professionals trained in a common language reduces retaining costs. Productivity: IS professionals can learn SQL thoroughly and become proficient. And, since they are familiar with the language in which the programs are written, programmers can easily maintain existing programs Application portability: Applications can be moved from machine to machine when each machine used SQL. It is economical for the computer software industry to develop application software when there is standard language. Application longevity: A standard language tends to remain for a long time. Hence there will be little pressure to rewrite old applications. Reduced dependencies on a single vendor: When a non-proprietary language is used, there may be thousands of vendors. The market for such vendors will be more competitive, which may decrease prices and improve service Cross-system communication: Different DBMSs and application programs can more easily communicate and co-operate in managing data.
  • 4
    xplain the SQL environment The SQL environment includes An instance of SQL DBMS Programs that use the DBMS to access database A catalog which consists database Users who work with database Most companies keep at least 2 versions of any database they are using One is product version & other is Development version Product version must be tightly controlled and monitored Development version is not tightly controlled and monitored Each database will have a named schema - associated with a catalog Users can access the information from a database using programs which include SQL queries Catalog: It is a set of schemas that, when put together, constitutes a description of a database Schema: It is a structure which contains descriptions of objects (table/view/constraints etc) created by a user as a part of database Cdtd/0R.•Dev Data USERS Programs SQ Queries Users User schema Catalog: Prod xplain Nested Queries /NON Correlated Sub Queries
  • 5
    Non correlated sub query involves placing a query with in another query. The inner query provides values to the outer query. A query with sub query is called nested query. A nested query can have multiple sub queries EX: The following query finds the maximum salary of the employees from EMP table SELECT MAX(SAL) FROM EMP WHERE MAX(SAL) FROM EMP); PLEASE LEARN & WRITE EXAMPLES AND ITS DESCRIPTION FROM THE EXAMPLE'S Ex Iain Correlated nested ueries/CorreIated Sub Queries? Correlated query is the one which processing the inner query depends on the data from outer query. Here, the inner query must be computed for each outer row. EX: The following query prints the employees who draw first four maximum salaries. SELECT ENAME,SAL FROM EMP E WHERE COUNT(SAL) FROM EMP WHERE E.SAL
  • 6
    Dual is a table that is created by Oracle together with data dictionary. It is suitable for in selecting a pseudo column such as SYSDATE or USER. The table has a single VARCHAR2 (1) column called DUMMY that has a value of 'X'. DUAL was originally a table and the database engine would perform disk 10 on the table when selecting from DUAL. Example: SQL> desc dual; NULL? Type NAME DUMMY VARCHAR2(1) The DUAL table is used because the relational model does not have a placeholder for calculations because every command must be a SQL statement. Note that the outer query references the dummy table called "dual" the dual table is used in Oracle when one need to run SQL that does not logically have a table name. Example: SELECT USER FROM DUAL; SELECT SYSDATEFROM DUAL; To select current user ID from dual and the current date from dual. The Dual table is also used for date retrieving arithmetic Expressions. This last query displays the day of the week for any date in the past 1,000 years. To see the day of the week that you were born, copy this query into your c: directory, add your birth data, and run the query see what day of the week you were born on. SQL NULL VALUE: The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. NULL values represent missing unknown data. By default, a table column can hold NULL values. If a column in a table is optional, one can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value. NULL values are treated differently from other values. NULL is used as a place hold for unknown 0" inapplicable values. Note: It is not possible to compare NULL and 0; they are not equivalent. It is not possible to test for NULL values with comparison operators, such as =,
  • 7
    The column can contain Null value only if the not null constrain is not mentioned in the creation processes. The value of the selected record should be left empty using selected insert method. (by entering the data only for the selected column leaving other column NULL) Don't type 0 or space it is not considered as NULL. Syntax: The basic syntax of NULL while creating a table: SQL> CREATE TABLE EMPLOYEE (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID)); Here NOT NULL signifies that column should always accept an explicit value of the given data type. There are two column where NOT NULL is not used which means these column can contain a NULL value. A field with a NULL value is one that has been left blank during record creation. rite short notes on VIEWS or VIRTUAL TABLES? In SQL, a view is a representation of one or more tables. A view can be used to hide the complexity or relationship between tables to provide security for sensitive data of the database. In general, a view is a part of table and it describes only certain portion of table. A view can contain all rows or selective rows of a table. After a table created and populated with data, it may become necessary to prevent all users from accessing all columns of the table for data security reasons. For this reason, SQL allows to create a new object, called a view. Logically a view is a subset of a table, the table on which we create a view, is called a base table. We can create several views on a single base table. The reasons to create a view are: When data security is required To minimize the data redundancy Easy maintenance of database Once a view has been created on a base table, it can be queried exactly like a table. Creating a view: Views can be created by CREATE VIEW command. Syntax: coln from where Create view as select coll, c012 Consider the following EMPLOYEE table From the above EMPLOYEE table we can create a separate view for each dept as follows SQL>create view sales view as select* from employee where dept+' sales'; SQL> create view marketing view as select* from employee where dept='marketing'; Here two separate views are created. Once a view is created we can insert, delete, update and drop accordingly. Inserting data into view:
  • 8
    Once a view is created we can insert a row similar to table INSERT command as follows. SQL>insert into sales view values (15,'abhishek',2-dec-89', 20000,'sales'); Updating a view: Views can also be used for data manipulation. These views are called updatable views. When we update a view, modification to data will be passed to underlying base table. When we update a view, we can see the modifications in its base table. Updating a view is similar to updating a table SQL>update sales view set salary=12000 where eno=12; Deleting Data from View: We can also delete a row from a view. If we delete a row from a view, the same row will be deleted from its base table also. Deleting a row from a view also similar to deleting a row from a table SQL> delete from sales view where eno=13; Dropping a view: A view can bed roped like a table if it's no longer required. SQL>drop view sales view: Explain Built-in functions in SQL Xerox will be provided Explain briefly about the operators in SQE Note: Students has to learn example for each clause. Arithmetic Operators Comparison Operators Logical Operators SET Operators Special Operators + Plus - Minus Multiplication / Division = Equal to != or Not equal to > Greater than, < less than >= greater than or equal to,
  • 9
    WHERE DISTINCT GROUP BY ORDER BY HAVING UNION UNION ALL INTERSECT MINUS Includes the conditions for row selection within a single table or multiple tables Avoids duplicate rows Groups rows according to category specified Sorts the final results rows in ascending/descending order Ca only be used with a GROUP BY and acts as a secondary WHERE clause, returns only these groups, which meet a specified condition Combines unique records with matching attributes of multiple tables Combines I records with matching attributes of multiple tables including duplicats Retrieves common records with matching attributes of multiple tables Retrieves other than common records with matching attributes from the first table DATA DEFINITION LANGUAGE (DDL) DDL commands are used to define a database including creating, altering & dropping tables and establishing constraints. DDL deals with Metadata. The commands are: CREATE, ALTER, DROP, TRUNCATE and RENAME
  • 10
    CREATE Command Purpose: To create Oracle database objects such as tables, views, indexes, clusters, and synonyms etc. Syntax: CREATE TABLE table_name (column _ name datatype(size) [CONSTRAINT constraint _ name] [constraint _ type] ) • Creating and Copying Tables: Example: To create a table student with rno, name, marks attributes. CREATE TABLE STUDENT (RNO NUMBER(2), NAME VARCHAR2(20), MARKS NUMBER(3); After creating a table, the structure of that may be verified by DESC command: SQL> DESC STUDENT; Name RNO NAME MARKS Null? Type NUMBER(2) VARCHAR2(20) NUMBER(3) Example: To copy one table to another table. To copy emp table to emp 1. CREATE TABLE EMP 1 AS SELECT * FROM EMP ; Example: To copy limited rows of a table to another table. The following query copies query copies the records of the employees whose salary >2000from emp table to emp2 CREATE TABLE EMP2 AS SELECT * FROM EMP WHERE SAL>2000; Example: To copy limited of a table to another table. The following query copies only empno, ename, job, sal columns from emp table to emp3. CREATE TABLE EMP3 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP; Creatin Tables with Constraints NOT NULL UNIQUE PRIMARY KEY DEFAULT CHECK FOREIGN KEY (REFERENCES) CONSTRAINTS IN SQL Enforce the value of a column to be not null i.e., it has to be have value Enforces the value of a column to be unique i.e., the column cannot have duplicate values but it allows null value Enforces the value of a column to be unique and to be not null i.e., the column cannot have duplicate values and null values Provides a default values for a column when a value is not supplied for that Used for checking the values of a column against the conditions specified Allows only the values matching to the values to another primary key column of another table or the same table Example: To create a table with primary key, check and not null constraints. Roll number should not allow duplicate values, Name cannot be null and marks must be in student table. CREATE TABLE STUDENT, (RNO NUMBER(2) PRIMARY KEY, NAME VARCHAR2(20) NOT NULL, MARKS NUMBER(3) CHECK, Example: To create a table with foreiqn key (references) constraint.
  • 11
    Values of dept _ num attribute of department table should match with values of dept _ num attributes of employee table. CREATE TABLE DEPARTMENT (DEPT_NUM NUMBER(2) PRIMARY KEY, DEPT_NAME VARCHAR2(20)); CREATE TABLE NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(20), JOB VARCHAR2(20), SALARY NUMBER(4), DEPT_NUM NUMBER(2) REFERENCES Example: To create a table with default and unique constraint. Values of dept _ num attribute should be unique and default value for the dept_name should be "ACCOUNTS" in department table. CREATE TABLE DEPARTMENT (DEPT_NUM NUMBER(2) UNIQUE, DEPT_NAME VARCHAR2(20) DEFAULT 'ACCOUNTS'); Suppose, if you try to insert a record without any value for dept_name. INSERT INTO VALUES(IO); The record "10, ACCOUNTS" is entered into the table i.e. it takes "ACCOUNTS" as default value for dept_name, even if we do not enter. Example: To create a table with composite unique constraint. Combination of item _ num and item _ name attributes should not be duplicated in items table. CREATE TABLE ITEMS (ITEM_NUM NUMBER (2), ITEM_NAME VARCHAR2 (20), CONSTRAINT Cl UNIQUE (ITEM_NUM, ITEM_NAME)); Example: To create a table with named unique constraint i.e. there should be a name for unique constraint. CREATE TABLE DEPATTMENT (DEPT_NUM NUMBER(2) CONSTRAINT Cl UNIQUE, DEPT_NAME VARCHAR2 (20)), Creating Indexes: Example: To create an index on a column. To create alphabetical index on customer _ name attribute of customer table. (CUSTOMER_NAME); CREATE INDEX CUST INDEX ON CUSTOMER Example: To create an index on multiple columns deptno and dname CREATE INDEX DEPT_NAME ON DEPT (DEPTNO, DNAME); Creating Views: Example: To create a view of manager of emp table CREATE VIEW MANAGERS AS SELECT* FROM EMP WHERE JOB='MANAGER' Example: To create a view of multiple tables. View should have empno, ename, job attributes from emp table and deptname from dept table. CREATE VIEW EMPVIEW AS SELECT EMPNO, ENAME, JOB, DNAME FROM EMP, DEPT WHERE EMP. DEPTNO= DEPT.DEPTNO
  • 12
    ALTER Command Purpose: To alter the structure of a database object Syntax: ALTER TABLE table_name (ADD column_name datatype(size)) (MODIFY column _ name datatype(size)) (DROP COLUMN column_name); Altering Tables: Example: To add a new column to an existing table. ALTER TABLE STUDENT ADD (AVERAGE Example: To modify the lenqth of an existinq column of a table. The following query modifies the length ot the existing attribute rno to '3' in student table. ALTER TABLE STUDENT MODIFY RNO NUMBER(3); Example: To add constraint an existinq column of a table. The following query adds constraint primary key to the existing attribute rno of student table. ALTER TABLE STUDENT ADD CONSTRAINT Cl PRIMARY KEY(RNO) Example: To remove an existinq column of a table. The following query removes the existing attribute "average" of student table. ALTER TABLE STUDENT DROP COLUMN AVERAGE; Example: To remove constraint on existinq column of a table. The following query removes the constraint Cl of student table. ALTER TABLE STUDENT DROP CONSTRAINT Cl; Example: To enable/disable constraint on existinq column of a table. The following query enables/disab/es the constraint Cl of student table. ALTER TABLE STUDENT ENABLE CONSTRAINT Cl; ALTER TABLE STUDENT DISABLE CONSTRAINT Cl; TRUNCATE Command Purpose: To delete rows with auto commit Syntax: TRUNCATE TABLE table-_name; Deleting Information from a Table Example: to delete complete information from a table TRUNCATE TABLE STUDENT; RENAME Command Purpose: To rename a database object Syntax: RENAME old _ database-name TO new_table_name; Renaming a Table:
  • 13
    Example: to rename a table RENAME STUDENT TO STUDI; DATA MANIPULATION LANGUAGE (DML) DML commands are used to maintain and access a database, including updating, inserting, modifying and querying data. It deals with data. The commands are SELECT, INSERT, UPDATE & DELETE. SELECT Command Purpose: To retrieve data from a table, and it allows filtering. Syntax: SELECT (DISTINCT) column_list FROM table_list (WHERE condition) (GROUP_BY grouping_columns )(HAVING group _ condition )(ORDER_BY orderby_columns ); Selecting Records Example: To select complete information from the table student. SELECT *FROM STUDENT; Example: To select records o the em 10 ees whose salar >= 2000 from emp table (limited rows) SELECT *FROM STUDENT WHERE Example: To select name, lob, salary from the emp table (limited columns). SELECT ENAME, JOB, SAL FROM STUDENT; Example: To select names of all manaqers from the emp table (limited columns and rows). SELECT ENAME FROM STUDENT WHERE JOB='MANAGER'; Example: To print details of the employees who joined in 'February' and the year 1981. SELECT * FROM EMP WHERE TO_CHAR (HIREDATE, "MON-YYYY') -'FEB-1981'; Example: To print names and jobs of managers and salesmen but salesman must be printed as "sales person" and "manager" must be printed as "boss" SELECT ENAME, SALES PERSON', 'MANAGER','BOSS') JOB FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') Using Operators Example: To calculate total of ml, m2 and m3 attributes of student table. Heading of the result Should be "TOTAL" (Column Alias) SELECT Ml+M2+M3 TOTAL FROM STUDENT; Example: To compute 2+3 *4 (gives 14) SELECT 2+3*4 FROM DUAL;
  • 14
    Example: To select records of the from emp table SELECT * FROM EMP WHERE JOB='CLERK' AND Example: To select records of the employees whose comm. Is null from emp_table. SELECT * FROM EMP WHERE COMM IS NULL; Example: To select records of the employees whose salary is between 2000 and 4000 from emp table. SELECT * FROM EMP WHERE SAL IS BETWEEN 2000 AND 4000 Example: To select records of Smith, James and Allen from emp table. SELECT* FROM EMP WHERE ENAME IN ( 'JAMES', 'SMITH', 'ALLEN')' Example: To select records of employees who ioined before '30-iun-81 and '31-dec-81' from emp table. SELECT* FROM EMP WHERE HIRE-DATE NOT BETWEEN '30-JUN-81' AND '31-DEC-81'; Using Wildcard Characters (%-): Example: To select records whose names start with 'S' from emp table. SELECT * FROM EMP WHERE ENAME LIKE 's%'• '0/0 is used to replace any number of characters Example: To select records whose names have only '4' characters from emp table. SELECT * FROM EMP WHERE ENAME LIKE ' (Underscore) is used to replace any one character. Example: To select records of the employees whose names have ' i' as second character SELECT * FROM EMP WHERE ENAME LIKE 'O/oi%'• SORTING: Using "Order By" Clause: Example: To list the employee details in ascendinq order accordinq to salary. SELECT * FROM EMP ORDER BY SAL; Example: To list the employee details in descendinq order accordinq to names. SELECT * FROM EMP ORDER BY ENAMEQESC; Example: To list the employee details in ascending order according to names. If there is a matching name, then sort by empno. SELECT* FROM EMP ORDER BY ENAME, EMPNO; Example: To list the employee details in ascending order according to their experience SELECT ENAME, JOB, MONTHS BETWEEN (SYSDATE, HIREDATE)12 FROM EMP ORDER BY 3• "ORDER BY 3' indicates sorting according to third column i.e. experience in years GROUPING: Using "Group By" and "Having" Clause: Example: To list the department numbers and number of employees in each department. SELECT DEPTNO, FROM EMP GROUP BY DEPTNO;
  • 15
    Example: To list the total salary, maximum salary, minimum salary and the averaqe salary of employees iob- wise. SELECT JOB, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL) FROM EMP GROUP BY JOB; Example: To list the lobs and number of employees in each lob. The result should be in ascendinq order accordinq to the number of employees SELECT JOB, FROM EMP GROUP BY JOB ORDER BY 2; Example: To list the lobs, which are done by minimum of 2 persons SELECT JOB FROM EMP GROUP BY JOB HAVING Example: To list the department number in which maximum employees work SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT = (SELECT MAX FROM EMP GROUP BY DEPTNO) Example: To list the department number and number of clerks in each department. SELECT DEPTNO, FROM EMP WHERE JOB: 'CLERK' GROUP BY DEPTNO Example: To list the Unique lobs in emp table. SELECT JOB FROM EMP GROUP BY JOB; The same can be written using "DISTINCT" clause as; SELECT DISTINCT JOB FROM EMP; Using SET OPERATORS Assume STDI and STD2 are tables with same attributes name, rno and marks with few common and few unique records. Example: To list combined set of unique records from multiple tables SELECT NAME,RNO, MARKS FROM STDI UNION SELECT NAME, RNO MARKS FROM STD2; Example: To list combined set of records from multiple tables including duplicates SELECT NAME, RNO, MARKS FROM STDI UNION ALL SELECT NAME, RNO, MARKS FROM STD2; Example: To list common records from multiple tables SELECT NAME, RNO, MARKS FROM STDI INTERSECT SELECT NAME, RNO,MARKS FROM STD2; Example: To list the records (of first table) other than common records of multiple tables. SELECT NAME, RNO, MARKS FROM STDI MINUS SELECT NAME, RNO, MARKS FROM STD2;
  • 16
    INSERT Command Purpose: To retrieve data from a table, and it allows filtering. Syntax: INSERT INTO table_name (column_list) VALUES (value_list); Inserting Records Example: To insert a record into emp table. INSERT INTO EMP VALUES (7777, 9000, NULL,20); Example: To insert only emp number, ename, lob and salry INSERT INTO EMP (EMPNO, ENAME, JOB, SAL) VALUES (8888,'KlRAN' ,'CLERK' ,4000); Example: To insert a record throuqh parameter substitution. INSERT INTO EMP(EMPNO, ENAME,JOB, SAL) When you execute the above query, it will ask for the values ofA,B,C and D as follows: Enter value for A: 7666 Enter value for B: SHYAM Enter value for C: MANAGER Enter value for D: 9500 Then, the record with the given values will be inserted. Example: To insert the result set of a query in a table. INSERT INTO STD2 (RNO, NAME, MARKS) SELECT RNO, NAME, MARKS FROM STDI; With the above query, records ofSTD1 will be inserted into STD2 UPDATE Command Purpose: To update the values of attributes Syntax: UPDATE table name SET Column_name=value(,column_name=value (WHERE condition); Updating Records Example: To set Smith's salary to 8000. UPDATE EMP SET SAL -8000 WHERE ENAME='SMITH', Example: To increase the salary of employees by 10% UPDATE EMP SET SAL Example: To update values of multiple attributes at a time UPDATE EMP SET SAL: 8000, COMM=IOO WHERE JOB='MANAGER';
  • 17
    DELETE Command Purpose: To delete rows from a table Syntax: DELETE FROM condition); Deleting Records Example: To delete the records of clerks DELETE FROM EMP WHERE JOB='CLERK' Example: To delete all records of a table DELETE FROM STDI; (or DELETE STDI;)
  • 18
    DATA CONTROL LANGUAGE (DCL) DCL commands are used to control a database, including administering previleges and the committing (saving) of data. It deals with accessibility and transaction changes. The commands are GRANT, REVOKE, COMMIT ROLLBACK and SAVEPOINT. GRANT Command Purpose: To give privileges on database objects to other users Syntax: GRANT privilege_list role ON object TO user_list public (WITH GRANT OPTION); Granting Permission: Example: To qrant all permissions on emp table to evervbodv GRANT ALL ON EMP TO PUBLIC; Example: To qrant Select, Delete permissions on dept table to ravi GRANT SELECT, INSERT ON DEPT TO RAVI; Example: To qrant al/ permissions on emp table to Kiran. Allow him to qive further qrants on emp to other users. GRANT ALL ON EMP TO KIRAN WITH GRANT OPTION; REVOKE Command Purpose: To cancel privileges on database-objects to other users Syntax: REVOKE privilege-list role ON object FROM user public; Revoking Permission: Example: To revoke Update privileqe from everybody on emp table REVOKE UPDATE ON EMP FROM PUBLIC; Example: To revoke all privileqes on emp from ravi REVOKE ALL ON EMP FROM RAVI; COMMIT Command Purpose: To make the changes permanent Syntax: COMMIT;
  • 19
    Saving Changes: Example: To delete records of clerks and save the chanqe on emp table. DELETE FROM EMP WHERE JOB='CLERK' COMMIT; We will not be able to retrieve the records of clerk back. ROLLBACK Command Purpose: To cancel the changes Syntax: ROLLBACK Cancelling Changes: Example: To delete records of clerks and retrieve them back >DELETE FROM EMP WHERE JOB='CLERK' > ROLLBACK We will retrieve the records of clerk back. SAVEPOINT Command Purpose: To set a margin for commit or rollback Syntax: SAVEPOINT savepoint_name; Saving or Cancelling Changes up to a Point: Example: >DELETE FROM EMP WHERE JOB='MANAGER' >SAVEPOINT PI; >DELETE FROM EMP WHERE JOB='CLERK' >ROLLBACK TO PI;
  • 20
    ADVANCED SQL COMMANDS: Join is a relational algebra operation that causes 2 or more relations with a common domain to be combined into a single relation. To explain this concept, let us consider two relations say STD and COURSE Equi-Join: JOIN Equi-Join Non-Equi Join ft Outer Join Outer Join -Right Outer Join Self Join SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (z) is used as comparison operator in the where clause to refer equality. Example: To list the values of rno, name, course id and cows name from student and course tables by joining them. SELECT RNO, NAME, STD, CID, COURSE, CID,CNAME •0M STD, COURSE WHERE STD. CID-COURSE.CID; NON Equi-ioin: A join in which the following cond Example: To list the names of the students -SELECT RNO, NAME, CNAME, Fi IS based l" on-equality be en values in the common columns. 'd subjects not known them in alphc tical order 'VI STD, COURSE WHERE s, D ER BY NAME. Outer join: A jolt"] friäch rows the "I do not have matching va es in common columns are nevertheless i ruded in the result ta. It include t [I aft outer join and right Left outer-Join: It displays all the recci Example: To list the values of rno, name, course id Right Outer-Join: or join -SELECT RNO, NAME, STD.CID, CNAME FROM D, COURSE WHERE It displays all the records of student tablog- Example: i course id in the student table. ,urse name from student and course tables by joining them. there is no matching course id in the course table. To list the values of rno, name, course id and course name from student and course tables by joining them. It should display all the records of student table even if there is no matching course id STD.CID -COURSE.CID(+) SELECT RNO, NAME, STD.CID, CNAME FROM STD, COURSE WHERE Self-Join (joining a table to itself): A join in which a table is joined to itself, where joining condition is based on columns of a same table. Example: To list the names of the employees and their managers from emp table. SELECT EI.ENAME "WORKER", Q.ENAME"MANAGER" FROM EMP El, EMP Q WHERE El.EMPNO=E2.MGR;
  • 21
    Sub Queries: Example: To find the second max salary of the employees in emp table. SELECT MAX(SAL) FROM EMP WHERE SAL< (SELECT MA(SAL) FROM EMP); Example: To print the name of the employee who draws maximum salary. SELECT ENAME FROM EMP WHERE MAX(SAL) FROM EMP); Example: To print the name of the department in which maximum employees work. SELECT DNAME FROM DEPT WHERE DEPTNO IN (SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING (SELECT FROM EMP GROUP BY DEPTNO)); Example: To print the names, which have maximum number of characters, >SELECT ENAME FROM EMP WHERE FROM EMP); Example: To list the lobs, which is done by maximum no. of persons. SELECT JOB FROM EMP GROUP BY JOB HAVING = (SELECT MAX (COUNT ( FROM EMP GROUP BY JOB); Example: To list the name, job, hire date, deptno of the recently ioined employees in each department SELECT NAME, JOB, HIREDATE, DEPTNO FROM EMP WHERE (HIREDATE, DEPTNO) IN (SELECT MAX(HIREDTE), DEPTNO FROM EMP GROUP BY DEPTNO) Example: To list the names and hire date of employees who joined on the same day. SELECT ENAME, HIRE-DATE FROM EMP WHERE HIRE-DATE IN (SELECT HIRE-DATE FROM EMP GROUP BY HIRE-DATE HAVING ENAME JAMES FORD HIRE-DATE 03-DEC-81 03-DEC-81 Example: To list the year in which more number of employees have joined >SELECT FROM EMP GROUP BY HAVING (SELECT FROM EMP GROUP BY TO_ Example: to list the deptno, deptname and maximum salary of each department SELECT DISTINCT EMP. DEPTNO, DEPT.DNAME.EMP.SAL FROM EMP, DEPT WHERE EMP. DEPTNO=DEPTNO AND (EMP.DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO) DEPTNO 10 20 30 DNAME ACCOUNTING RESEARCH SALES SAL 5000 3000 2850
  • 22
    Examples: To list the employee name, deptno, and salary of the employees, who earn maximum salary in each department >SELECT ENAME, DEPTNO, SAL FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM GROUP BY DEPTNO) Using derived table: Example: To list the name, PF, DA and total salary per year SELECT ENAME, TSAL, TSAL*O.I TSAL FROM (SELECT "TSAL" FROM EMP) Example: To print the employees who draw first three maximum salaries SELECT ENAME, SAL FROM EMP WHERE SAL IN (SELECT SALI FROM (SELECT DISTINCT SAL SALI FROM EMP), (SELECT DISTINCT SAL SAL2 FROM EMP) WHERE GROUP BY SALI HAVING ORDER BY SAL DESC Correlated Sub Queries: Example: To print the employees who draw first three maximum salaries SELECT ENAME, SAL FROM EMP E WHERE (SELECT COUNT(SAL) FROM EMP WHERE E.SAL

Discussion

Copyright Infringement: All the contents displayed here are being uploaded by our members. If an user uploaded your copyrighted material to LearnPick without your permission, please submit a Takedown Request for removal.

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related PPTs

Query submitted.

Thank you!

Drop Us a Query:

Drop Us a Query