1 / 104

Welcome you to the world of SQL-DBMS

Welcome you to the world of SQL-DBMS. INDEX. INDEX. INDEX. INDEX. INDEX. INDEX.

hera
Télécharger la présentation

Welcome you to the world of SQL-DBMS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Welcome you to the world of SQL-DBMS

  2. INDEX

  3. INDEX

  4. INDEX

  5. INDEX

  6. INDEX

  7. INDEX

  8. A DBMS consist of a Collection of interrelated data and a set of programs to access that data. A set of programs that is used to store and manipulate data. Manipulation of data includes- adding new data , deleting unwanted data , changing existing data. • A DBMS is a software package to facilitate the creation and maintenance of a computerized database. • DBMS contains information about a particular enterprise • DBMS provides an environment that is both convenient and efficient to use. • Some available database management systems are DB2,Oracle,MS Access, SQL Server, Ingres, Postgre SQL, MYSQL , Berkeley DB, Times Ten, Rdb

  9. Collection of logical constructs used to represent data structure and relationship within the database. • System designers develop models to explore ideas and to improve the understanding of database design. • There are three types of database models

  10. Record based data model is also called as logical or implementation model. This model is again divided in the three models . And they are Hierarchical ,Network, Relational model.

  11. Logically represented by an upside down tree • Each parent can have many children • Each child has only one parent

  12. Each record can have multiple parents • Composed of sets • Each set has owner record and member record • Member may have several owners

  13. Relational Database Management System – a database system made up of files with data elements in two-dimensional array (rows and columns). This database management system has the capability to recombine data elements to form different relations resulting in a great flexibility of data usage. • Data and relationships are represented as tables each has a number of columns relating to fields or attributes. • Perceived by user as a collection of table for database. A relational database uses two dimensional tables to store information. Tables are a series of row/column intersections. • Tables related by sharing common entity characteristic(s) • There is a relationship between the two tables. • SQL is used to manipulate the relational databases.

  14. The principles of the relational model were first outline by Dr. E. F. Codd in a June 1970 paper title “ A Relational Model Of Data For Large Shared Data Banks”. In this paper Dr . Codd proposed the relational model for database system. It is the basis for the relational database management system. • The relational model contains the following components: 1. Collection of objects or relations. 2. Set of operations to act on the relations. 3. Data integrity for accuracy and consistency.

  15. Each database requires at least one database administrator to administer it. Because a DBMS can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility. • A database administrator's responsibilities can include the following tasks: 1.Installing and upgrading the Oracle server and application tools 2.Allocating system storage and planning future storage requirements for the database system 3.Creating primary database storage structures (table spaces) after application developers have designed an application

  16. A DBMS is partitioned into modules that deal with each of the responsibilities of the overall system. • The design of DBMS must include consideration of interface between database system and operating system • Figure shows the three level or tier architecture proposed by ANSI/SPARC.

  17. A Functional components of DBMS can be divided into two categories as follows 1.Query Processor 2.Storage Manager • Query processor component consist of DML compiler, Embedded DML Compiler , DDL Interpreter, Query evaluation engine • Storage manager component consist of Authorization and integrity manager, Transaction manager, File manager , Buffer manager

  18. SQL stands for Structured Query Language. • Common Language For Variety of Databases. SQL uses combination of relational algebra and calculus constructs. It can define the structure of data, modify data and specify security constraints. Both ANSI and ISO have accepted SQL as the standard language for relational database. • SQL commands are divided into categories, depending upon what they do DML-Data Manipulation Language, DDL -Data Definition Language, DCL -Data Control Language • The SQL language is subdivided into several language elements, including: Clauses, which are constituent components of statements and queries. Expressions, which can produce tables consisting of columns and rows of data Predicates, which specify conditions that are used to limit the effects of statements and queries, or to change program flow. Queries, which retrieve the data based on specific criteria. Statementscan control transactions, program flow, connections and sessions. SQL statements also include the semicolon (";") statement terminator.

  19. SQL Statements DML = SELECT , INSERT ,UPDATE , DELETE , MERGE DDL = CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMIT DCL/Data Administration = GRANT,REVOKE Transaction Control/Application Generation=COMMIT, ROLLBACK, SAVEPOINT

  20. Basic SELECT statement In the above syntax, SELECT  is a list of one or more column *  selects all column Column|Expression  selects the named column or expression Alias  gives selected column different headings FROM table  specify the table containing the column ;  defines the end of an SQL statement SELECT * |{ Column | Expression [alias] , ….} FROM table ;

  21. SELECT statement defines WHAT is to be returned (separated by commas) • Database columns (From Tables or Views) • Constant Text Values • Formulas • Pre-defined Functions • Group Functions (COUNT, SUM, MAX, MIN, AVG) • “*” mean all columns from all tables In the FROM Statement • Example: SELECT region_name, .. • FROM statement defines the Table(s) or View(s) used by the SELECT or WHERE Statements • You MUST Have a FROM statement • Multiple Tables/Views are separated by Commas

  22. Column Alias • Renames a column heading. • Is useful with calculation. • Immediately follows the column name.(there can also be the optional AS keyword between the column name and alias) • Requires double quotation marks if it contain spaces ,special character or if it is case sensitive When we run this query , what output we actually get is a ‘table’ which has column name as NAME, SURNAME, Annual Salary WHERE Clause • WHERE clause is optional. When used it defines what records are to be included in the query. Syntax , SELECT first_name AS name, last_name surname, salary*12 “Annual Salary” FROM employees; SELECTcolumn_name, column_name,… FROMtable_name WHEREcondition(s);

  23. WHERE  restricts the query to rows that meet a condition Condition  is composed of column name, expression, constants and comparison operator Example This query gives us a table named Customer which contain all column but shows only those records whose country is Mexico WHERE clause may consist of • Conditional Operators [=, >, >=, <, <=, != (<>)=, (<>)] • BETWEEN x AND y • IN (list) • LIKE ‘‘%string’’ • IS NULL or NOT {BETWEEN / IN / LIKE / NULL} • Multiple conditions linked with AND & OR statements • Strings contained within SINGLE QUOTES!! • An Alias can not be used in the WHERE clause. SELECT * FROM CustomersWHERE Country = 'Mexico‘ ;

  24. Comparison operatorsare used in the conditions that compare one expression to another expression or value. They are used in the WHERE clause Syntax ….WHERE expr operator value ; Example …WHERE last_name = ‘Joshi’; …WHERE salary > (or)>=(or)<(or)<= 8000; BETWEEN…AND… OPERATOR This operator is used to display rows based on a range of values.The range that you specify contains a lower limit and upper limit. And you must specify lower limit first. Example …WHERE salary BETWEEN 4500 AND 8500; …WHERE name BETWEEN ‘NEHA’ AND ‘GITA’;

  25. IN ( SET ) This operator is used to test for values in the list. The IN operator can be used with any data type. If character or dates are used in the list, they must be enclosed in a single quotation mark (‘ ’). Example …WHERE emp_id IN (100 ,101 ,201 ); The result of this query will be a table with specified columns for all the records whose emp_id is 100 , 101 or 201 LIKE The LIKE operator is used to perform a wildcard searches of valid search string value. You can select rows that match a character pattern by LIKE operator. Pattern matching is referred to as a wildcard search. Search condition can contain literal characters, numbers. %  denotes zero or many character _  denotes one character Example …WHERE first_name LIKE ‘s%’; This query gives the table that shows all the specified record whose first name starts with ‘s’.

  26. IS NULL This operator is used to test for null. A null value means the value is unavailable, Unassigned, unknown or inapplicable Example …WHERE student_marks IS NULL ; This query gives us a table ,that shows specified column with all the records that contain a null value for student_marks column. Concatenation operator Using this (||)operator we can link column to other column, arithmetic expression or constant values to create a character expression. Example This query gives us a table having column name Employee and record shows the concatenation of last name and job id. SELECT last_name || job_id AS “ Employee” FROM employees ;

  27. LOGICAL OPERATOR A logical operator combines the result of two components condition to produce a single result based on those Conditions or it inverts the result of single condition . A row is return only if the overall result of the condition is true. Three logical operators are AND,OR ,NOT AND And operator requires both conditions to be true. Example This query gives us a table having specified column and only those records that have salary more than 10000 and job_id contain a string …MAN.. SELECT last_name , job_id , salary FROM employees WHERE salary >= 10000 AND job_id LIKE ‘ %MAN% ’ ;

  28. OR OR operator requires either condition to be true. Example In this example either condition to be true for any record to be selected. Therefore any employee who has a job id that contain a string ‘MAN’ or earns 10000 or more is selected. NOT NOT operator returns value if the condition is false. NOT operator can be used with comparison operators e.g. NOT IN (SET) , IS NOT NULL , NOT LIKE , NOT BETWEEN..AND.. Example …WHERE job_id NOT IN (ST_CLERK , SA_REP); …WHERE salary NOT BETWEEN 35000 AND 6000; …WHERE last_name NOT LIKE ‘ %A% ’ ; SELECT last_name , job_id , salary FROM employees WHERE salary >= 10000 OR job_id LIKE ‘ %MAN% ’ ;

  29. Arithmetic Operator Arithmetic operators are used to create an expression with number and date data. You may need to modify the way in which data is displayed or may want to perform calculations. They are possible with addition , subtraction ,multiplication , division. Following are the examples for arithmetic expression. In the result table we get a third column salary+500 and column contain the added salary for every record in the table. SELECT last_name , salary , salary+500 FROM employees; SELECT last_name, salary, 12*salary + 500 FROM employees;

  30. ORDER BY CLAUSE The order of rows that are return in a query result is undefined . The ORDER BY clause can be used to sort the rows . If you use the ORDER BY clause , It Must be the last clause of the SQL statement. Syntax [ ORDER BY {column , expression , numeric _position}[ASC|DESC] ]; ORDER BY  specifies the order in which the retrieved rows are displayed ASC  orders the rows in ascending order(default order) DESC  orders the rows in descending order Example In the table ,we can see “priority” Column is in ascending order SELECT * FROM reunion ORDER BY priority ASC ;

  31. Function are a very powerful feature of SQL and can be used to do the following : Perform calculations on data Modify individual data items Manipulate output for group of rows Format date and number for display Convert column data types SQL function may accept argument and always return a value. • There are two distinct types of function • Single-row function • Multiple-row function

  32. Single-Row Function:- These functions operate on single row only and returns one result per Row . There are different type of single row function. This lesson covers the following ones. Character Number Date Conversion • Multiple-row function:- These function manipulate groups of rows to give one result per group of rows.

  33. Single row functions It Manipulate data items , Accept arguments and return one value , Act on each row returned , Return one result per row , May modify the datatype , Can be nested. Syntax Function_name(column| expression, [arg1 , arg2 ,………]) They accept one or more argument and return one value for each row returned by the query. An argument can be one of the following : user supplied constant Variable value , Column name , Expression • Character function : Accept character input and can return both character and number values • Number function : Accept numeric input and return numeric values • Date function : Operate on values of the date datatype (All date function , which returns number.) • Conversion Function : convert a value from one datatype to another

  34. Single-Row character Function accept character data as input and can return both character and number values . Character function can be divides into the following • Case conversion function • Character manipulation function • Case Conversion Function LOWER , UPPER ,and INITCAP are the three case conversion function LOWER:- convert mixed case or uppercase character string to lowercase UPPER :-convert mixed case or lowercase character string to uppercase INITCAP:- converts first letter of each word to uppercase and remaining letter to lowercase

  35. Character Manipulation Functionsare CONCAT , SUBSTR , LENGTH ,INSTR, LPAD and TRIM CONCAT :- join values together (you are limited to using two parameters with CONCAT.) SUBSTR :- extracts a string of determined length LENGTH:- show the length of a string as a numeric value INSERT :- finds numeric position of named character LPAD:- pads the character value right-justified RPAD :character manipulation function pads the character value left-justified TRIM: trim heading or trailing character from character string. If trim _charcater or trim_source is character literal, you must enclose it in single quotes In this image we can see how character functions are used.

  36. Number function accept numeric input and return numeric values . This section describe some of the number function. ROUND, TRUNC, MOD are the number functions. In this image we can see, how number functions work.

  37. The ROUND function rounds the column , expression , or value to n decimal places . if the second argument is 0 or is missing , the value is rounded to two decimal places .If the second argument is 2,the value is rounded to the 2 decimal places. Conversely , if the second argument is -2 , the value is rounded to two decimal place to the left. • The ROUND function can also be used with date function . • The DUAL is a dummy table .the DUAL table is generally used for SELECT statement.

  38. The TRUNC function truncates the column , expression , or value to n decimal place. • The TRUNC function works with argument similar to those of the ROUND function . if the second argument is 0 or is missing , the value is truncated to zero decimal places. • If the second argument is 2 ,the value is truncated to two decimal places. • Conversely , if the second argument is 2 , the value is truncated to two decimal places to the left • Like the ROUND function , the TRUNC function can be used with date function

  39. The MOD function finds the remainder of the first argument divided by second argument. The MOD function is often used to determine if the value is odd or even. See the example ,

  40. In SQL the default date format is DD-MON-RR. • It enables you to store 21st century dates in the 20th century by specifying only the last two digits of the year. • It enables you to store 20th century dates in the 21st century in the same way. • When a record with the date column is inserted into a table, the century information is picked up from the SYSDATE function. SYSDATE is a function that returns a date and time. Following are the date function.

More Related