Download to read offline
![Authors: Elmasri and Navathe
Fundamentals of Database Systems , 7e
Copyright
©
2017
Pearson
India
Education
Services
Pvt.
Ltd
UPDATE
UPDATE <Table Name> SET
<attribute>=‘value/Expression’ [WHERE clause];
Used to modify attribute values of one or more
selected tuples
A WHERE-clause selects the tuples to be
modified
An additional SET-clause specifies the attributes
to be modified and their new values
Each command modifies tuples in the same
relation](https://image.slidesharecdn.com/unit-3-sql-part1-230404034326-2851da22/85/Unit-3-SQL-part1-ppt-24-320.jpg)
![Authors: Elmasri and Navathe
Fundamentals of Database Systems , 7e
Copyright
©
2017
Pearson
India
Education
Services
Pvt.
Ltd
DELETE
Removes tuples from a relation
DELETE <Table Name> [WHERE clause];
Includes a WHERE-clause to select the tuples to be
deleted
Tuples are deleted from only one table at a time
A missing WHERE-clause specifies that all tuples in
the relation are to be deleted; the table then becomes
an empty table
The number of tuples deleted depends on the number
of tuples in the relation that satisfy the WHERE-clause](https://image.slidesharecdn.com/unit-3-sql-part1-230404034326-2851da22/85/Unit-3-SQL-part1-ppt-27-320.jpg)
![Authors: Elmasri and Navathe
Fundamentals of Database Systems , 7e
Copyright
©
2017
Pearson
India
Education
Services
Pvt.
Ltd
UPDATE
UPDATE <Table Name> SET
<attribute>=‘value/Expression’ [WHERE clause];
Used to modify attribute values of one or more
selected tuples
A WHERE-clause selects the tuples to be
modified
An additional SET-clause specifies the attributes
to be modified and their new values
Each command modifies tuples in the same
relation](https://image.slidesharecdn.com/unit-3-sql-part1-230404034326-2851da22/75/Unit-3-SQL-part1-ppt-24-2048.jpg)
![Authors: Elmasri and Navathe
Fundamentals of Database Systems , 7e
Copyright
©
2017
Pearson
India
Education
Services
Pvt.
Ltd
DELETE
Removes tuples from a relation
DELETE <Table Name> [WHERE clause];
Includes a WHERE-clause to select the tuples to be
deleted
Tuples are deleted from only one table at a time
A missing WHERE-clause specifies that all tuples in
the relation are to be deleted; the table then becomes
an empty table
The number of tuples deleted depends on the number
of tuples in the relation that satisfy the WHERE-clause](https://image.slidesharecdn.com/unit-3-sql-part1-230404034326-2851da22/75/Unit-3-SQL-part1-ppt-27-2048.jpg)
More Related Content
Unit-3-SQL-part1.ppt
- 1. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd CHAPTER 6 Basic SQL
- 2. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Chapter 6 Outline SQL Data Definition and Data Types Specifying Constraints in SQL Basic Retrieval Queries in SQL INSERT, DELETE, and UPDATE Statements in SQL Additional Features of SQL
- 3. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Basic SQL SQL language Considered one of the major reasons for the commercial success of relational databases SQL The origin of SQL is relational predicate calculus called tuple calculus (see Ch.8) which was proposed initially as the language SQUARE. SQL Actually comes from the word “SEQUEL” which was the original term used in the paper: “SEQUEL TO SQUARE” by Chamberlin and Boyce. IBM could not copyright that term, so they abbreviated to SQL and copyrighted the term SQL. Now popularly known as “Structured Query language”. SQL is an informal or practical rendering of the relational data model with syntax
- 4. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd SQL Data Definition, Data Types, Standards Terminology: Table, row, and column used for relational model terms relation, tuple, and attribute CREATE statement Main SQL command for data definition Base tables (base relations) Relation and its tuples are actually created and stored as a file by the DBMS
- 5. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd The CREATE TABLE Command in SQL Specifying a new relation Provide name of table Specify attributes, their types and initial constraints General Format CREATE TABLE <Table Name> (Attribute1 type constraint, Attribute2 type constraint, ……….); Eg: CREATE TABLE EMPLOYEE (EmpNo Char(4) NOT NULL, Ename Varchar2(20),Bpay number(8,2));
- 6. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Attribute Data Types in SQL Basic data types Numeric data types Integer numbers: INTEGER, INT, and SMALLINT Floating-point (real) numbers: FLOAT or REAL, and DOUBLE PRECISION Character-string data types Fixed length: CHAR(n), CHARACTER(n) Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER VARYING(n)
- 7. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Attribute Data Types and Domains in SQL (cont’d.) Bit-string data types Fixed length: BIT(n) Varying length: BIT VARYING(n) Boolean data type Values of TRUE or FALSE or NULL DATE data type Ten positions Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD Multiple mapping functions available in RDBMSs to change date formats
- 8. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Attribute Data Types and Domains in SQL (cont’d.) Additional data types Timestamp data type Includes the DATE and TIME fields Plus a minimum of six positions for decimal fractions of seconds Optional WITH TIME ZONE qualifier INTERVAL data type Specifies a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp DATE, TIME, Timestamp, INTERVAL data types can be cast or converted to string formats for comparison.
- 9. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd INSERT In its simplest form, it is used to add one or more tuples to a relation Attribute values should be listed in the same order as the attributes were specified in the CREATE TABLE command Constraints on data types are observed automatically Any integrity constraints as a part of the DDL specification are enforced
- 10. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd The INSERT Command Specify the relation name and a list of values for the tuple. All values including nulls are supplied. The variation below inserts multiple tuples where a new table is loaded values from the result of a query.
- 11. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd INSERT – add or insert raw into a table General format of INSERT INSERT INTO <table name> VALUES (value1,value2,value3,………); Numeric values – just enter the number eg 25 String data – value insider “ “ eg: “Saji”
- 12. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Eg; INSERT COMMAND CREATE TABLE student (stno number(2), name char(20), m1 number (4,2), m2 number (4,2), m3 number(4,2)); INSERT INTO student VALUES (01,”Mahesh”,,35,40.75);
- 13. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd SELECT Command - To Display the content of a table Format of command SELECT < */list of columns/formula> FROM <table name>; * - display all columns values list of columns – displaying required columns values only Formula – the value of the formula will be displayed
- 14. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Student - stno,name,m1,m2,m3 To display the whole table content SELECT * FROM student; To display the content of stno and m2 SELECT stno,m2 FROM student; To display the content name, total mark SELECT name,m1+m2+m3 FROM student;
- 15. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd The SELECT-FROM-WHERE Structure of Basic SQL Queries Basic form of the SELECT statement:
- 16. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd The SELECT-FROM-WHERE Structure of Basic SQL Queries (cont’d.) Logical comparison operators =, <, <=, >, >=, and <> Eg: Select * from student where M1>20 AND M2>20; Two simple condition can be combined by using AND and OR operator AND – combined condition true when both conditions are true Eg: Select * from student where M1>=20 AND m2>=20;
- 17. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd (2+4)*6
- 18. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd AND , OR operator in SELECT OR – combined condition true when either of the conditions are true Eg: Select * from student where M1>20 OR m2>20; Changing Priority order by using bracket AND operator will get higher priority than OR To change the priority bracket can be used Select * from Employee where dept = ‘Sales’ AND desig =‘Manager’ OR bpay > 5000
- 19. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd AND , OR operator in SELECT To change the priority bracket can be used Select * from Employee where dept = ‘Sales’ AND dept =‘Purchase’ OR bpay > 5000 Select * from Employee where dept = ‘Sales’ AND (dept =‘Purchase’ OR bpay > 5000)
- 20. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Avoiding Duplicates SELET DISTINCT dept from Employee; - Display the different dept from Employee Using IN and NOT IN operator SELECT * FROM employee where dept IN (‘Sales’,’Purchase’,’FINACE’) SELECT * FROM employee where dept NOT IN (‘Sales’,’Purchase’)
- 21. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Using BETWEEN operator Select * from Student where M1>20 and M1<40;
- 22. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Sorting Records in a table – ORDER BY SELECT * FROM STUDENT ORDER BY M1; - Sorting student records in ascending order of M1 SELECT * FROM STUDENT ORDER BY NAME DESC; - descending order of Name
- 23. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Sorting Records in a table – ORDER BY SELECT * FROM STUDENT ORDER BY NAME ASC, M1 DESC; - ASCENDIG ORDER OF NAME AND WHEN NAME REPEATS IN DESCENDING ORDER OF M1 SELECT * FROM STUDENT WHERE M1>25 ORDER BY M1; - using where clause to selectively choose records and then sorting
- 24. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd UPDATE UPDATE <Table Name> SET <attribute>=‘value/Expression’ [WHERE clause]; Used to modify attribute values of one or more selected tuples A WHERE-clause selects the tuples to be modified An additional SET-clause specifies the attributes to be modified and their new values Each command modifies tuples in the same relation
- 25. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd UPDATE (contd.) Example: Give all employees with empno 10, to10% raise in salary. UPDATE EMPLOYEE SET BPAY = BPAY *1.1 WHERE EMPNO = 10; In this request, the modified SALARY value depends on the original SALARY value in each tuple
- 26. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd UPDATE (contd.) Example: Change the location and controlling department number of project number 10 to 'Bellaire' and 5, respectively UPDATE PROJECT SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10
- 27. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd DELETE Removes tuples from a relation DELETE <Table Name> [WHERE clause]; Includes a WHERE-clause to select the tuples to be deleted Tuples are deleted from only one table at a time A missing WHERE-clause specifies that all tuples in the relation are to be deleted; the table then becomes an empty table The number of tuples deleted depends on the number of tuples in the relation that satisfy the WHERE-clause
- 28. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd The DELETE Command Removes tuples from a relation Includes a WHERE clause to select the tuples to be deleted. The number of tuples deleted will vary.
- 29. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Basic Retrieval Queries
- 30. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Basic Retrieval Queries (Contd.)
- 31. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Specifying Constraints in SQL Basic constraints: Relational Model has 3 basic constraint types that are supported in SQL: Key constraint: A primary key value cannot be duplicated Entity Integrity Constraint: A primary key value cannot be null Referential integrity constraints : The “foreign key “ must have a value that is already present as a primary key, or may be null.
- 32. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Specifying Attribute Constraints Other Restrictions on attribute domains: Default value of an attribute DEFAULT <value> NULL is not permitted for a particular attribute (NOT NULL) CHECK clause Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);
- 33. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Specifying Key and Referential Integrity Constraints PRIMARY KEY clause Specifies one or more attributes that make up the primary key of a relation Dnumber INT PRIMARY KEY; UNIQUE clause Specifies alternate (secondary) keys (called CANDIDATE keys in the relational model). Dname VARCHAR(15) UNIQUE;
- 34. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Specifying Key and Referential Integrity Constraints (cont’d.) FOREIGN KEY clause Default operation: reject update on violation Attach referential triggered action clause Options include SET NULL, CASCADE, and SET DEFAULT Action taken by the DBMS for SET NULL or SET DEFAULT is the same for both ON DELETE and ON UPDATE CASCADE option suitable for “relationship” relations
- 35. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Giving Names to Constraints Using the Keyword CONSTRAINT Name a constraint Useful for later altering
- 36. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Default attribute values and referential integrity triggered action specification (Fig. 6.2)
- 37. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Specifying Constraints on Tuples Using CHECK Additional Constraints on individual tuples within a relation are also possible using CHECK CHECK clauses at the end of a CREATE TABLE statement Apply to each tuple individually CHECK (Dept_create_date <= Mgr_start_date);
- 38. Authors: Elmasri andNavathe Fundamentals of Database Systems , 7e Copyright © 2017 Pearson India Education Services Pvt. Ltd Basic Retrieval Queries in SQL SELECT statement One basic statement for retrieving information from a database SQL allows a table to have two or more tuples that are identical in all their attribute values Unlike relational model (relational model is strictly set-theory based) Multiset or bag behavior Tuple-id may be used as a key