slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQL PowerPoint Presentation
Download Presentation
SQL

Loading in 2 Seconds...

play fullscreen
1 / 18

SQL - PowerPoint PPT Presentation


  • 139 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL' - darrel-riggs


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SQL

STRUCTURED QUERY LANGUAGE

e r diagram

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

constraints
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 .

slide4

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 .

slide5

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

slide6

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

rdbms relational database management system
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

sql statement
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)
select dql
Select (DQL) :
  • Capabilities of SQL SELECT statement :

Projection : selecting column Selection :selecting rows

tab 1 tab 2

Join

basic select statement
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.

writing sql statement
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 ;

arithmetic expression
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 :

operator precedence
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 .
slide15
DDL

DATA DEFINATION LANGUAGE

the create table statement
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 .

creating tables
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));