cpsc 3220 the language of sql n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CpSc 3220 The Language of SQL PowerPoint Presentation
Download Presentation
CpSc 3220 The Language of SQL

Loading in 2 Seconds...

play fullscreen
1 / 23

CpSc 3220 The Language of SQL - PowerPoint PPT Presentation


  • 102 Views
  • Uploaded on

CpSc 3220 The Language of SQL. Chapter 17 Modifying Data. SQL handles CRUD operations. The DML part of the SQL language allows the implementation of all CRUD operations C - create: handled by the INSERT statement R – retrieve: handled by the SELECT statement

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 'CpSc 3220 The Language of SQL' - donny


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
cpsc 3220 the language of sql

CpSc 3220The Language of SQL

Chapter 17

Modifying Data

sql handles crud operations
SQL handles CRUD operations
  • The DML part of the SQL language allows the implementation of all CRUD operations
  • C - create: handled by the INSERT statement
  • R – retrieve: handled by the SELECT statement
  • U – update: handled by the UPDATE statement
  • D – delete: handled by the DELETE statement
the insert statement
The INSERT statement
  • INSERT statements allow in insertion of rows of data into an existing table
  • Each inserted row must be unique, i.e., it must contain the primary key
  • Some tables are defined to have auto-increment PKs and the DBMS will insert those values automatically
  • Values need not be inserted for all columns, columns for which no value is inserted are set to NULL
  • Insertions can be constrained by tables rules; i.e., PK and FK constraints
basic forms of the insert statement
Basic Forms of the INSERT statement
  • There are two basic types of INSERT statements
    • Insert specific data that is included explicitly in the INSERT statement
    • Insert data that is obtained from a SELECT statement (a subquery)
general format for type one insert
General Format for Type One INSERT

INSERT INTO tableName

[(columnList)]

VALUES

(row1Values) [,(row2Values) . . .] ;

example of type one insert
Example of Type One INSERT

INSERT INTO Student

(ID, name, dept_name, tot_cred)

VALUES

('12127','Howard','Comp. Sci.','0') ;

example of type one insert1
Example of Type One INSERT

insert into student

values

('54444','Wilson','Comp. Sci.', '0');

example of type one insert2
Example of Type One INSERT

insert into student

(ID,name,tot_cred)

values

('44499','Wilson', '0');

example of type one insert3
Example of Type One INSERT

insert into student

(name,ID,tot_cred)

values

('Bixby','44999', '0');

example of type one insert4
Example of Type One INSERT

INSERT INTO Student

(ID, name, dept_name, tot_cred)

VALUES

(12127,’Howard’,,0);

example of type one insert5
Example of Type One INSERT

INSERT INTO Student

(ID, name, tot_cred)

VALUES

(12127,’Howard’,0);

example of type one insert6
Example of Type One INSERT

INSERT INTO Student

(ID,tot_cred, name)

VALUES

(12127, 0, ’Howard’);

example of type one insert7
Example of Type One INSERT

insert into student

values

('64445','Wilson','Comp. Sci.', '0'),

('74446','Jackson','Comp. Sci.', '0'),

('74447','George','Comp. Sci.', '0'),

('74440','James','Comp. Sci.', '0'),

('74440','Andrews','Comp. Sci.', '0');

error in type one insert
Error in Type One INSERT

insert into student

values

('64445','Wilson','Comp. Sci.', '0'),

('74446','Jackson','Comp. Sci.', '0'),

('74447','George','Comp. Sci.', '0'),

('74440','James','Comp. Sci.', '0'),

('74440','Andrews','Comp. Sci.', '0');

error in type one insert1
Error in Type One INSERT

insert into student

values

('64445','Wilson','Comp. Sci.', '0'),

('74446','Jackson','Comp. Sci.', '0'),

('74447','George','Comp. Sci.', '0'),

('74440','James','Comp. Sci.', '0'),

('74448','Andrews','CompSci.', '0');

general format for type two
General Format for Type Two

INSERT INTO tableName

[(columnList1)]

SELECT columnList2

FROM tableName2

WHERE condition;

example of type two insert
Example of Type Two INSERT

insert into student

(ID,name,dept_name,tot_cred)

select i.id,i.name,'Comp. Sci.','0'

from instructor as i

where dept_name='History';

the delete statement
The DELETE statement
  • Even simpler than the UPDATE
  • General form

DELETE

FROM tableName

[WHERE

condition]

  • There is an alternate form to delete an entire table:

TRUNCATE TABLE tableName

the update statement
The UPDATE statement
  • A bit more complex
  • We must specify the columns to be updated, give an expression that specifies the update, and include logic for determining which rows are to be updated.
  • All data can be supplied in the UPDATE statement
  • Some data may be determined by subqueries
general form of an update statement
General Form of an UPDATE statement

UPDATE table

SET colName1 = expression1

[,colName2 = exp2 . . . ]

WHERE condition

an example of an update statement
An Example of an UPDATE statement

UPDATE instructor

SET salary = salary * 1.5

WHERE dept_name = 'Comp. Sci.';

another example of an update statement
Another Example of an UPDATE statement

UPDATE takes

SET grade = 'A+'

WHERE course_id = 12345;

that s it
That’s It!
  • You know all the CRUD about SQL, the DML part
  • But you don’t know how to create at database
  • That’s what we look at next; the DDL part of SQL
  • Read Chapter 18 of the Rockoff textbook