1 / 18

SQL

SQL. STRUCTURED QUERY LANGUAGE. Relationship. E-R-DIAGRAM :. Composite attribute. N. Work for. L NAME. M. location. name. F name. Department. NAME. Multivalued attribute. Entity. ssn. number. 1. No.of employee. Mgr. 1. EMPLOYEE. Key attribute. Employer.

darrel-riggs
Download Presentation

SQL

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. SQL STRUCTURED QUERY LANGUAGE

  2. Relationship E-R-DIAGRAM : Composite attribute N Work for L NAME M location name F name Department NAME Multivalued attribute Entity ssn number 1 No.of employee Mgr 1 EMPLOYEE Key attribute Employer Derived attribute Dependent of Identifying weak relationship name Super vision N 1 dependent Relationship

  3. CONSTRAINTS • These are the restriction or condition that are used on the column of the table to preserve the data correctness . • Constraints type are following : • Unique • Check • Not null • Primary key • Foreign key • Not null • This ensure that at least some values should be present for an attribute Can have more than one not null on a table . Null : Null is neither zero or blank space . It is used to represent empty . Ex : Name cannot be left blank/null in table .

  4. Unique : • It checks for duplicate values a unique . • A unique column can have multiple null value . Ex : employee id / mobile / mail id . Check : Check is used for enforcing some additional condition with respect to business Requirement . Ex : Sal > 3000 , Age >14 . Primary key : • It is used for identifying a record uniquely in a table . It is the combination of “ Not null “ and “ Unique” constraints • You can have only one primary key is allowed per table . Ex : Employee id or mobile / mail id can be used as primary key .

  5. Note :…. • Candidate key : Eligible to become a primary key • Primary key : Unique and Not null . • Alternate key : Eligible to become a primary key but not chosen as primary key. • when “p k” is created out of more than one column then it is called as composite primary key. C k = P k + A k A k = C k – P k

  6. Foreign key : It is referential integrity constraints which created the relationship between the table . To create a foreign key in a child table , Master table should have a primary key on the common column . composite primary key in master table is used as composite foreign key in child table . Foreign key can take both null and duplicate value .There can be more than one foreign key in Table . child table Master table

  7. RDBMS : Relational database management system • 2-dimensional architecture • If client is directly connected to database then it is 2-tier Database management system (DBMS) . Database server client

  8. SQL STATEMENT : • One language with 5 sub languages : - • DQL (Data query language ) • DML (Data manipulation language) • DDL (Data definition language) • TCL/DTL (Data transaction language / Transaction control language) • DCL (Data control language)

  9. Select (DQL) : • Capabilities of SQL SELECT statement : Projection : selecting column Selection :selecting rows tab 1 tab 2 Join

  10. Basic select statement : SELECT identifies what column. FROM identifies which table. SELECT * |{ [ DISTINCT ] Column / Expression [ alias ]….} |FROM Table ; Ex : select empno ,ename ,sal from emp ; ENAME EMPNO SAL ---------- ---------- ---------- SMITH 7369 800 ALLEN 7499 1600 WARD 7521 1250 JONES 7566 2975 MARTIN 7654 1250 BLAKE 7698 2850 CLARK 7782 2450 SCOTT 7788 3000 KING 7839 5000 9 rows selected.

  11. Writing SQL Statement: • SQL Statement are not case sensitive . • SQL Statement can be one or more lines . • Keyword can not be abbreviated or split across lines . • Clauses are usually placed on separate lines . • / (forward slash) is used in the place of semi colon but if it is in the next line • SQL also use arithematic operations . like : select ename,sal,sal+500 from emp ; select ename ,sal ,sal *12 from emp ;

  12. Arithmetic expression : Create expression with number and date data by using arithmetic operators . Question : Calculate a salary increase of 300 for all employees and display Both old and new salary ? Answer :

  13. Operator Precedence : * , / ,+ , - • Multiplication and Division take priority before addition and subtraction • Operator of the same priority are evaluated from left to right . • Parentheses (Brackets) are used to force prioritized evaluation and to clarify statements . • If we are doing any arithmetic operation with null then it is always null . • If we are adding two column and value of one column is null then the final value is null .

  14. DDL DATA DEFINATION LANGUAGE

  15. The create table statement : CREATE TABLE [ schema.] table (column data type [DEFAULT Expression] [;……] ) ; You must have : CREATE TABLE privilege You specify : Table name column name ,column data type and column size and constraints .

  16. Creating tables Create table dept 1 ( dept no number (2), dname varchar2 (14), loc varchar2 (13)); Create table emp 1 (emp no number (4) primary key, Ename varchar2 (10) not null, Age number (10) check (age > 14), Mobile number (10) unique, Dept no number (2) reference dept (dept no));

More Related