UNIT 3 DMS.pdf

Unit3: Interactive SQL and Advance SQL Database Management Systems  Introduction to In-built Function A In-built function is an expression in which an SQL keyword or special operator executes some operation. In-built functions use keywords or special built-in operators. Built-i...

Unit3: Interactive SQL and Advance SQL Database Management Systems  Introduction to In-built Function A In-built function is an expression in which an SQL keyword or special operator executes some operation. In-built functions use keywords or special built-in operators. Built-ins are SQL92Identifiers and are case- insensitive. Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data.  Types of In-built Function. SQL has many built-in functions for performing processing on string or numeric data. − Numeric functions allow for manipulation of numeric values. Numeric functions are sometimes called mathematical functions. Numeric Function Examples of Numeric Function.  String Functions SQL string functions are used primarily for string manipulation  String Functions are used to perform an operation on input string and return an output string. CHAR_LENGTH(): Doesn’t work for SQL Server. Use LEN() for SQL Server. This function is used to find the length of a word. Syntax: SELECT char_length('Hello!'); Output: 6 ASCII(): This function is used to find the ASCII value of a character. – Syntax: SELECT ascii('t'); – Output: 116 CONCAT(): This function is used to add two words or strings. – Syntax: SQL> SELECT CONCAT('My', 'S', 'QL'); – Output: ‘MySQL’ LEFT(str,len) – Returns the leftmost len characters from the string str, or NULL if any argument is NULL. – Syntax: SQL> SELECT LEFT('foobarbar', 5); – Output: fooba  INSTR(str,substr): Returns the position of the first occurrence of substring substr in string str. REVERSE(str):Returns the string str with the order of the characters reversed. STRCMP(str1,str2):Compar es two strings and returns 0 if both strings are equal, it returns -1 if the first argument is smaller than the second according to the current sort order otherwise it returns 1.  Date and Time Function The date and time functions use a subset of IS0- 8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS“. Examples of Date and Time Function  Aggregate Functions An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*), aggregate functions ignore null values All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.  1. COUNT FUNCTION – to Count the number of rows in a database table – It can work on both numeric and non-numeric data types. – COUNT(*) that returns the count of all the rows in a specified table. – COUNT(*) considers duplicate and Null. SUM Function – Sum function is used to calculate the sum of all selected columns. It works on numeric fields only. AVG function – The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values. MAX Function – MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column. MIN Function – MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.  Aggregate Functions: Things to remember MySQL supports all the five (5) ISO standard aggregate functions COUNT, SUM, AVG, MIN and MAX. SUM and AVG functions only work on numeric data. If you want to exclude duplicate values from the aggregate function results, use the DISTINCT keyword. The ALL keyword includes even duplicates. If nothing is specified the ALL is assumed as the default. Aggregate functions can be used in conjunction with other SQL clauses such as GROUP BY  GROUP BY Clause Sometimes, rather than retrieving individual records, you want to know something about a group of records. In SQL, GROUP BY Clause is one of the tools to summarize or aggregate the data series. The GROUP BY Clause is utilized in SQL with the SELECT statement to organize similar data into groups. It combines the multiple records in single or more columns using some functions. Generally, these functions are aggregate functions such as min(),max(),avg(), count(), and sum() to combine into single or multiple columns. It uses the split-apply-combine strategy for data analysis. – In the split phase, It divides the groups with its values. – In the apply phase, It applies the aggregate function and generates a single value. – In the combine phase, It combines the groups with single values into a single value.  GROUP BY Clause is utilized with the SELECT statement. GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc. GROUP BY returns only one result per group of data. GROUP BY Clause always follows the WHERE Clause. GROUP BY Clause always precedes the ORDER BY Clause  Having Clause HAVING Clause utilized in SQL as a conditional Clause with GROUP BY Clause. returns rows where aggregate function results matched with given conditions only. It added in the SQL because WHERE Clause cannot be combined with aggregate results HAVING Clause always utilized in combination with GROUP BY Clause. WHERE and HAVING can be used in a single query. WHERE vs HAVING  ORDER BY clause In SQL, the ORDER BY keyword is used to sort results in ascending or descending order according to the values of one or more columns. By default ORDER BY will sort in ascending order. If you want to sort the results in descending order, you can use the DESC keyword and and the keyword ASC to sort in ascending order. You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list. ORDER BY returns sorted items in ascending and descending order while GROUP BY returns unique items with the aggregate resultant column. GROUP BY and ORDER BY together  Joins The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. Consider the following two tables −  Inner Join The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN. The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join- predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. Example of Inner Join. Inner join between two tables.  Outer Join The SQL OUTER JOIN returns all rows from both the participating tables which satisfy the join condition along with rows which do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition only. The subtypes of SQL OUTER JOIN LEFT OUTER JOIN or LEFT JOIN RIGHT OUTER JOIN or RIGHT JOIN FULL OUTER JOIN Example of Outer Join  Left Outer Join The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. Query of Left Outer Join  Right Outer Join The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with NULL in each column from the left table. This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate. Query of Right Outer Join  Full Outer Join In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Query of Full Outer Join  Concept of View In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. Views in SQL are kind of virtual tables A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. The SQL VIEW is, in essence, a virtual table that does not physically exist. It Restricts the access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.  Creating of View We can create View using CREATE VIEW statement. A View can be created from a single table or multiple tables. To see the data in the View, we can query the view in the same manner as we query a table  WITH CHECK OPTION The WITH CHECK OPTION clause in SQL is a very useful clause for views. It is applicable to a updatable view. If the view is not updatable, then there is no meaning of including this clause in the CREATE VIEW statement. The WITH CHECK OPTION clause is used to prevent the insertion of rows in the view where the condition in the WHERE clause in CREATE VIEW statement is not satisfied. If we have used the WITH CHECK OPTION clause in the CREATE VIEW statement, and if the UPDATE or INSERT clause does not satisfy the conditions then they will return an error.  Example In this View if we now try to insert a new row with null value in the NAME column then it will give an error because the view is created with the condition for NAME column as NOT NULL. For example,though the View is updatable but then also the below query for this View is not valid:  Deleting View SQL allows us to delete an existing View. We can delete or drop a View using the DROP statement.  Updating View There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then we will not be allowed to update the view. – The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause. – The SELECT statement should not have the DISTINCT keyword. – The View should have all NOT NULL values. – The view should not be created using nested queries or complex queries. – The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view. SQL>CREATE OR REPLACE VIEW MarksView AS SELECT StudentDetails.NAME, StudentDetails.ADDRESS, StudentMarks.MARKS, StudentMarks.AGE FROM StudentDetails, StudentMarks WHERE StudentDetails.NAME = StudentMarks.NAME;  Inserting a row in a view: Deleting a row from a View:  Views and Joins. A join view is a view which is based on more than one base table. Any insert, update or delete statement on a view can update only one of the base tables involved in the view. A table is called a key preserved in a join view, if the primary and unique keys are unique on the views result set. Sql> create view t1_view as select a.empno, a.ename, a.deptno, b.dname from emp a, dept b where a.deptno = b.deptno;  Sequences Sequence is a set of integers 1, 2, 3, … that are generated and supported by some database systems to produce unique values on demand. A sequence is a user defined schema bound object that generates a sequence of numeric values. Sequences are frequently used in many databases because many applications require each row in a table to contain a unique value and sequences provides an easy way to generate them. The sequence of numeric values is generated in an ascending or descending order at defined intervals and can be configured to restart when exceeds max_value.  Create a Sequence. The initial-value specifies the starting value for the Sequence. The increment-value is the value by which sequence will be incremented. The maximum-value specifies the upper limit or the maximum value upto which sequence will increment itself. The keyword CYCLE specifies that if the maximum value exceeds the set limit, sequence will restart its cycle from the begining. And, NO CYCLE specifies that if sequence exceeds MAXVALUE value, an error will be thrown.  Using Sequence in SQL Query Following is the sequence query creating sequence in ascending order. Following is the sequence query creating sequence in descending order. Example to use sequence : create a table named students with columns as id and name.  INDEXES Indexing is a data structure technique which allows you to quickly retrieve records from a database file An Index is a small table having only two columns. – a copy of the primary or candidate key of a table – set of pointers for holding the address of the disk block where that specific key value stored. An index takes a search key as input and efficiently returns a collection of matching records. Simply put, an index is a pointer to data in a table. An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. Indexes can be created or dropped with no effect on the data.  Different Indexing Strategies Dense Indexing: If an index entry is created for every search key value, then it is dense indexing. Sparse Indexing: If an index entry is created only for some records, then it is sparse indexing Dense and Sparse Index  Single level and Multilevel Index single-level indexing: the number of the index file is only one. multi-level indexing: sometimes the size of the index file becomes so large that the index file itself gets indexed.  Primary Index Primary Index is an ordered file which is fixed length size with two fields. – The first field is the same a primary key and – second field is pointed to that specific data block where primary key is available In the primary Index, there is always one to one relationship between the entries in the index table. Index created for the first record of each block is called block anchors.  Secondary indexing/Non-Clustered indexing The secondary Index in DBMS can be generated by a field which has a unique value for each record, and it should be a candidate key It is also known as a non-clustering index. a nonclustered index sorts and stores data separately from the data rows in the table Suppose you have a tabled called Employee in your database. The table has the following attributes: – employee_id(PRIMARY KEY) – employee_name – employee_department – employee_salary But while developing an application, you found out that most of the database queries are using the attribute employee_name. In that case, the primary indexing will not help much, and it will be a good practice to maintain separate indexing for all values belonging to employee_name  Clustered Indexing In a clustered index, records themselves are stored in the Index and not pointers. the Index is created on non-primary key columns which might not be unique for each record. In this index, we group two or more columns to get the unique values and create an index which is called clustered Index Let's assume that a company recruited many employees in various departments. In this case, clustering indexing in DBMS should be created for all employees who belong to the same dept.  Creating Index Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. Examples Dropping Indexes  SQL Aliases SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.  Synonyms a synonym is a noun that has a similar meaning as other words; just like intelligent has the same meaning as smart. SQL allows you to create a synonym so you can define an alternate name for an object that already exists The concept is analogous to the use of nicknames for friends and acquaintances Referencing an object owned by another user requires the schema name to be prefixed with it. With the help of a synonym, you reduce the effort of referencing the object along with the schema name. synonym provides location transparency because the synonym name hides the actual object name and its owner. At such situation, the CREATE SYNONYM statement allows you to create a synonym which is an alternative name for a database object such as a table, view, sequence, procedure, stored function, and materialized view  There are two categories of synonyms, public and private. A public synonym can be used to allow easy access to an object for all system users the individual creating a public synonym does not own the synonym-rather,it will belong to the PUBLIC user group Private synonyms, on the other hand,belong to the system user that creates them and reside in that user's schema. you need to have the CREATE SYNONYM privilege.Further, you must have the CREATE PUBLIC SYNONYM privilege in order to create public synonyms A) Creating a synonym within the same database example The following example uses the CREATE SYNONYM statement to create a synonym for the sales.orders table: Once the orders synonym is created, you can reference it in anywhere which you use the target object (sales.orders table).  B) Creating a synonym for a table in another database First, create a new database named test and set the current database to test Next, create a new schema named purchasing inside the test database Then, create a new table in the purchasing schema of the test database: After that, from the another database- Stores, create a synonym for the purchasing.suppliers table in the test database Finally, from the Stores database, refer to the test.purchasing.suppliers table using the suppliers synonym:  Dropping Synonyms To drop a private synonym, either the synonym must be in your own schema or you must have the DROP ANY SYNONYM system privilege. To drop a PUBLIC synonym, you must have the DROP PUBLIC SYNONYM system privilege. You must specify PUBLIC to drop a public synonym. You cannot specify schema if you have specified PUBLIC We can use the FORCE keyword to delete the synonym even if it has dependent tables or user-defined types.