Fresher training program relational database management system
Download
1 / 71

Fresher Training Program Relational Database Management System - PowerPoint PPT Presentation


  • 115 Views
  • Uploaded on

Fresher Training Program Relational Database Management System. Nguyen Minh Tien. Contents. DDL statements DML statements. Learning approach. The following are strongly suggested for a better learning and understanding of this course: Noting down the key concepts in the class

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 ' Fresher Training Program Relational Database Management System' - aldis


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
Fresher training program relational database management system

Fresher Training ProgramRelational Database Management System

Nguyen Minh Tien


Contents
Contents

  • DDL statements

  • DML statements


Learning approach
Learning approach

  • The following are strongly suggested for a better learning and understanding of this course:

    • Noting down the key concepts in the class

    • Analyze all the examples / code snippets provided

    • Study and understand the self study topics

    • Completion and submission of all the assignments, on time

    • Completion of the self review questions in the lab guide

    • Study and understand all the artifacts including the reference materials / e-learning / supplementary materials specified

    • Completion of the project (if application for this course) on time inclusive of individual and group activities

    • Taking part in the self assessment activities

    • Participation in the doubt clearing sessions


Sql structure query language
SQL – Structure Query Language

  • SQL is a language that all commercial RDBMS implementations understand.

  • SQL is a non-procedural language


Data types
Data Types

  • String data

    • CHAR(n) – string type with fixed length include n characters (maximum 2000 bytes).

    • VARCHAR2(n) – string type with changeable length include n characters (maximum 4000 bytes).

    • LONG – string with changeable length, maximum 4GB (only one column in a table).

  • Numeric data

    • NUMBER(p,q) – kiểu số có p ký số và q số lẻ.

    • INTEGER(p) – kiểu số nguyên có p ký số.

  • Date-time data

    • DATE – datetime type with fixed length in dd-mm-yy format


Constants literals
Constants/Literals

  • ANSI standard defines format for literals

  • Numeric: 21, -32, $0.75,1.2E4

  • String: enclosed within ‘ …’

  • Date : 12-mar-03*




NULL

  • Missing/unknown/inapplicable data represented as a null value

  • NULL is not a data value. It is just an indicator that the value is unknown


Statements
Statements

  • DDL: Data definition language (CREATE, ALTER, DROP, TRUNCATE)

  • DML: Data manipulation language (SELECT, INSERT, UPDATE, DELETE)

  • DCL: Data control language (COMMIT, ROLLBACK, SET TRANSACTION, GRANT, REVOKE)


SQL – DDL

Data Definition Language


Create table
CREATE TABLE

  • CREATE TABLE used to create structure of table.

  • Syntax:

    CREATE TABLE <table name>

    (<column_definition>, …

    [<table_constraint_definition>]);


Create table1
CREATE TABLE

  • Steps of creating a table

    • Step 1. Define data column.

    • Step 2. Column may contain NULL value??.

    • Step 3. Define columns has unique data (candidate key).

    • Step 4. Define primay key – foreign key.

    • Step 5. Define default values.

    • Step 6. Define constraint.

    • Step 7. Create table & index.


Create table check constraint
CREATE TABLE – CHECK constraint

  • CHECK constraint: used to specify constraint conditions to data. Whenever data changed (INSERT, UPDATE), these constraints are used to verify regular data.

  • Syntax:

    [CONSTRAINT tên_ràng_buộc]

    CHECK (điều_kiện)


Create table primary constraint
CREATE TABLE – PRIMARY constraint

  • PRIMARY constraint: used to specify primary key of table.

  • Syntax:

    [CONSTRAINT tên_ràng_buộc]

    PRIMARY KEY [(danh_sách_cột)]


Create table unique constraint
CREATE TABLE – UNIQUE constraint

  • UNIQUE constraint: used to define candidate keys for the table.

  • Syntax:

    [CONSTRAINT tên_ràng_buộc]

    UNIQUE [(danh_sách_cột)]


Create table foreign
CREATE TABLE – FOREIGN

  • FOREIGN constraint: used to define relationships between tables in the database.

  • Syntax:

    [CONSTRAINT tên_ràng_buộc]

    FOREIGN KEY [(danh_sách_cột)]

    REFERENCES tên_bảng_tham_chiếu(danh_sách_cột_tham_chiếu)

    [ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT]

    [ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT]


Create table exp
CREATE TABLE - Exp

CREATE TABLE lop

(

malop NVARCHAR(10) NOT NULL,

tenlop NVARCHAR(30) NOT NULL,

khoa SMALLINT NULL,

hedaotao NVARCHAR(25) NULL,

namnhaphoc INT NULL,

makhoa NVARCHAR(5),

CONSTRAINT pk_lop PRIMARY KEY (malop),

CONSTRAINT unique_lop_tenlop UNIQUE(tenlop)

)


Alter table
ALTER TABLE

  • ALTER TABLE

    • ALTER TABLE is used to change table structure: add/remove/update/change columns and constraints

      Syntax:

ALTER TABLE <Table_Name> ALTER COLUMN [<Column_name>

<New_data_type>]| ADD [<Column_name> <Data_Type>]

| DROP COLUMN [<Column _Name>]

With

<Table_Name> table name should be changed

<ALTER COLUMN> change column

<Column_Name> column name need to add, change or remove

<New_data_type> date type changed to column.

ADD add new column to table.

DROP COLUMN remove column from table.


Alter table exp
ALTER TABLE - Exp

CREATE TABLE nhanvien

(

manv NVARCHAR(10) NOT NULL,

hoten NVARCHAR(30) NOT NULL,

ngaysinh DATETIME,

diachi CHAR(30) NOT NULL

)

ALTER TABLE nhanvien

ADD

dienthoai NVARCHAR(6)

CONSTRAINT chk_nhanvien_dienthoai

CHECK (dienthoai LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')


Drop table
DROP TABLE

  • DROP TABLE

    • DROP TABLE statement is used to delete a table from DB, all relevant objects are removed: indexes, Triggers, Constraints.

      Syntax:

      With,

      <Table_Name> table name removed

DROP TABLE <Table_Name>


Drop table exp
DROP TABLE - Exp

  • Remove constraint firstly

    ALTER TABLE nhanvien

    DROP CONSTRAINT fk_nhanvien_madv

  • Remove table:

    DROP TABLE donvi


SQL – DML

Data Manipulation Language


Data manipulation language dml
Data Manipulation Language - DML

  • DML includes the following statements: SELECT, INSERT, UPDATE, DELETE, used to manipulate data in the database.

  • DML’s statements:

    • SELECT statement

    • INSERT statement

    • UPDATE statement

    • DELETE statement


Insert
INSERT

  • Syntax:

    INSERT INTO tablename VALUES (value list)

    Exp:

  • Single-row insert

    INSERT INTO Sinhvien VALUES(‘SV3’,’SUP3’,’BLORE’,10)

  • Inserting one row, many columns at a time

    INSERT INTO Sinhvien (MaSV, Hoten) VALUES (‘SV3’, ‘Smith’);

  • Inserting many rows, all/some columns at a time.

    INSERT INTO luusinhvien

    SELECT hodem,ten,ngaysinh

    FROM sinhvien

    WHERE noisinh like ‘%Huế%’


Specify column name in insert
Specify column name in INSERT

  • Must specify column’s name in INSERT statement

  • INSERT INTO Table_A (Col1, Col2, Col3)

    VALUES (‘1’, ‘2’, ‘3’)


Update
UPDATE

  • Syntax:

    UPDATE tablename SET column_name =value [ WHERE condition]

  • Exp:

    UPDATE S SET CITY = ‘KANPUR’ WHERE SNO=‘S1’

    UPDATE EMP SET SAL = 1.10 * SAL


Use case in update statement
Use CASE in UPDATE statement

UPDATE nhatkyphong

SET tienphong=songay*CASE

WHEN loaiphong='A' THEN 100

WHEN loaiphong='B' THEN 70

ELSE 50

END


Update data from tables
UPDATE data from tables

UPDATE nhatkybanhang

SET thanhtien = soluong*gia

FROM mathang

WHERE mathang.mahang

=(SELECT mathang.mahang

FROM mathang

WHERE mathang.mahang = nhatkybanhang.mahang)


Delete
DELETE

  • Syntax:

    DELETE FROM tablename WHERE condition

    Exp:

  • DELETE FROM SP WHERE PNO= ‘P1’

  • DELETE FROM SP


Use sub query in delete
Use sub-query in DELETE

DELETE FROM sinhvien

FROM lop

WHERE lop.malop=sinhvien.malop AND tenlop='Tin K24'

DELETE FROM lop

WHERE malop NOT IN (SELECT DISTINCT malop

FROM sinhvien)


Delete all data
Delete all data

DELETE FROM diemthi

Or

TRUNCATE TABLE diemthi


Select
SELECT

  • Syntax:

    SELECT [ALL/DISTINCT] <Column name1>, <Column name2>, …

    FROM <Table name>

    [WHERE <Search condition>]

    [GROUP BY grouping columns]

    [HAVING search condition]

    [ORDER BY sort specification]

    Exp:

    SELECT SNAME, CITY FROM S


Change column title
Change column title

SELECT 'Mã lớp'= malop,tenlop 'Tên lớp',

khoa AS 'Khoá'

FROM lop


Use case structure in select
Use CASE structure in SELECT

  • Syntax:

    CASE biểu_thức

    WHEN biểu_thức_kiểm_tra THEN kết_quả

    [ ... ]

    [ELSE kết_quả_của_else]

    END

SELECT masv,hodem,ten,

CASE gioitinh

WHEN 1 THEN 'Nam'

ELSE 'Nữ'

END AS gioitinh

FROM sinhvien


Using constant expression
Using constant & Expression

SELECT tenmonhoc,'Số tiết: ',sodvht*15 AS sotiet

FROM monhoc


Distinct top
DISTINCT & TOP

  • SELECT DISTINCT khoa FROM lop

  • SELECT TOP 5 hodem,ten,ngaysinh

    FROM sinhvien

  • SELECT TOP 10 PERCENT hodem,ten,ngaysinh

    FROM sinhvien


In and not in
IN and NOT IN

SELECT * FROM monhoc

WHERE sodvht=2 OR sodvht=4 OR sodvht=5

May be replaced by:

http://www.sqlviet.com/blog/cac-loai-join-trong-sql-server

SELECT * FROM monhoc

WHERE sodvht IN (2,4,5)



Like and not like1
LIKE and NOT LIKE

SELECT hodem,ten FROM sinhvien

WHERE hodem LIKE 'Lê%'

AND ten LIKE '[AB]%'


Create new table from select
Create new table from SELECT

SELECT hodem,ten,YEAR(GETDATE())-YEAR(ngaysinh) AS tuoi

INTO tuoisv

FROM sinhvien


Specify data existing in a table
Specify data existing in a table

  • Do not use COUNT(*) to specify a table has data or not:

    SELECT COUNT(*) FROM Table_A

  • Must use:

    SELECT TOP 1 FROM Table_A


Sorting
Sorting

  • Syntax:

    SELECT COL1,COL2,.......

    FROM TABLE_NAME

    WHERE <SEARCHCONDITION>

    ORDER BY COL-NAME [DESC]

    Example:

    SELECT CITY,COLOR,WEIGHT

    FROM P

    WHEREWEIGHT IN (12,17)

    ORDER BY 1 DESC, 2


Union
UNION

  • UNION is used to add data from many tables into one table

  • Syntax:

    UNION [ALL] Câu_lệnh_2

    [UNION [ALL] Câu_lệnh_3]

    ...

    [UNION [ALL] Câu_lệnh_n]

    [ORDER BY cột_sắp_xếp]

    [COMPUTE danh_sách_hàm_gộp [BY danh_sách_cột]]


Intersect
INTERSECT

  • INTERSECT operator return all rows that are common to multiple queries

  • The number of columns and the data types of the columns in the queries must be identical in all the SELECT statements used in the query.

  • Reversing the order of the intersected tables does not alter the result

  • INTERSECT does not ignore NULL values


Intersect1
INTERSECT

  • Display the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired (that is, they changed jobs but have now gone back to doing their original job)

    SELECT employee_id, job_id

    FROM employees

    INTERSECT

    SELECT employee_id, job_id

    FROM job_history;


Minus
MINUS

  • MINUS operator returns rows returned by the first query that are not present in the second query

  • The number of columns and the data types of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query.

  • All of the columns in the WHERE clause must be in the SELECT clause for the MINUS operator to work


Minus1
MINUS

  • Display the employee IDs of those employees who have not changed their jobs even once

    SELECT employee_id, job_id

    FROM employees

    MINUS

    SELECT employee_id, job_id

    FROM job_history;


JOIN

  • Inner join

  • Cross join

  • Outer join

    • Left-outer join

    • Right-outer join

  • Self join


Inner join
INNER JOIN

  • Common type of join

  • Combines records from two tables with matching values on a column.

  • Example:

    SELECT hodem,ten,ngaysinh

    FROM sinhvien INNER JOIN lop

    ON sinhvien.malop=lop.malop

    WHERE tenlop='Tin K24'


Outer join
OUTER JOIN

  • LEFT OUTER JOIN:

    SELECT e.last_name, e.department_id, d.department_name

    FROM employees e LEFT OUTER JOIN departments d

    ON (e.department_id=d.department_id);

  • This query retrieves all rows in the EMPLOYEES table, which is left table even if there is no match in the DEPARTMENTS table


Outer join1
OUTER JOIN

  • RIGHT OUTER JOIN

    SELECT e.last_name, d.department_name

    FROM employees e RIGHT OUTER JOIN departments d

    ON e.department_id=d.department_id;

  • This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no match in the EMPLOYEES table


Outer join 3
OUTER JOIN (3)

  • FULL OUTER JOIN:

    SELECT e.last_name, d.department_id, d.department_name

    FROM employees e FULL OUTER JOIN departments d

    ON (e.department_id=d.department_id);

  • This query retrieves all rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table


Self join
SELF JOIN

SELECT FIRST.SNO, SECOND.SNO

FROM S FIRST,

S SECOND

WHERE FIRST.CITY=SECOND.CITY

Get all pairs of SNO who are co-located


Cross join
CROSS JOIN

  • A join without any conditions is called a Cartesian product

  • It will be a collection of all possible combinations of rows from the tables involved

  • Practically of little use

  • Just included for conceptual completeness

  • Example:

    SELECT Hocvien.MaHV, Lop.Malop FROM Hocvien H, Lop P;


Aggregate functions
Aggregate functions

  • Used when information you want to extract from a table has to do with the data in the entire table taken as a set.

  • Aggregate functions are used in place of column names in the SELECT statement

  • The aggregate functions in SQL are: SUM(), AVG(), MAX(), MIN(), COUNT()



Sum function
SUM function

  • Adds up the values in the specified column

  • Column must be numeric data type

  • Value of the sum must be within the range of that data type

  • Example:

    SELECT SUM (QTY)

    FROM SP

    WHERE PNO=‘P2’


Avg function
AVG function

  • Returns the average of all the values in the specified column

  • Column must be numeric data type

  • Example:

    SELECT AVG(QTY)

    FROM SP

    WHERE SNO=‘S1’


Max function
MAX function

  • Returns the largest value that occurs in the specified column

  • Column need not be numeric type

  • Example:

    SELECT MAX(QTY)

    FROM SP

    WHERE SNO =‘S1’


Min function
MIN function

  • Returns the smallest value that occurs in the specified column

  • Column need not be numeric type

  • Example:

    SELECT MIN(QTY) FROM SP

    WHERE SNO=‘S1’


Count function
COUNT function

  • Returns the number of rows in the table

  • Example:

    SELECT COUNT(*) FROM S


Group by
GROUP BY

  • Related rows can be grouped together by GROUP BY clause by specifying a column as a grouping column.

  • GROUP BY is associated with an aggregate function

  • Example: For each part supplied get the part number and the total shipment quantity

  • Example:

    SELECT PNO, SUM(QTY) FROM SP GROUP BY PNO


Having
HAVING

  • Used to specify condition on group

  • Example:

    SELECT SNO , COUNT(*) FROM SP

    GROUP BY SNO

    HAVING COUNT(*)>=2


Independent sub queries
Independent sub-queries

  • Inner query is independent of outer query.

  • Inner query is executed first and the results are stored.

  • Outer query then runs on the stored results.


Using sub queries
Using sub-queries

SELECT SNAME

FROM S

WHERE SNO IN

(SELECT SNO

FROM SP

WHERE PNO =‘P2’)


Using sub queries 2
Using sub-queries (2)

SELECT SNO

FROM S

WHERE STATUS <

(SELECT STATUS

FROM S

WHERE SNO=‘S1’)


Using sub queries 3
Using sub-queries (3)

SELECT SNO

FROM S

WHERE STATUS <

(SELECT MAX(STATUS)

FROM S)


Using sub queries 4
Using sub-queries (4)

SELECT DISTINCT SNO

FROM SPJ X

WHERE PNO=‘P1’

AND QTY> (SELECT AVG(QTY)

FROM SPJ Y

WHERE PNO=‘P1’

AND X.JNO=Y.JNO)


Exist and not exist
EXIST and NOT EXIST

  • Used in sub-query

    SELECT hodem,ten

    FROM sinhvien JOIN lop on sinhvien.malop=lop.malop

    WHERE tenlop='Tin K25' AND

    year(ngaysinh)IN(SELECT year(ngaysinh)

    FROM sinhvien JOIN lop

    ON sinhvien.malop=lop.malop

    WHERE lop.tenlop='Toán K25')

    SELECT hodem,ten

    FROM sinhvien

    WHERE NOT EXISTS(SELECT masv FROM diemthi

    WHERE diemthi.masv=sinhvien.masv)



ad