1 / 20

SQL

SQL. Insert Update Delete Create table Alter table. 1. Insert. Add a new customer (c007, Scott, Boston 11) to customers table. Insert customers values ('c007', 'Scott', ‘Boston', 11). 1. Insert (cont). Add a new product (p08, keyboard, $10 ) to products table.

arden-burke
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 • Insert • Update • Delete • Create table • Alter table

  2. 1. Insert • Add a new customer (c007, Scott, Boston 11) to customers table. • Insert customers values ('c007', 'Scott', ‘Boston', 11)

  3. 1. Insert (cont) • Add a new product (p08, keyboard, $10 ) to products table. • insert products values ('p08', 'keyboard', , ,10) • insert products values ('p08', 'keyboard', null, null,10) • insert products (pid, pname, price) values ('p11', 'keyboard', 10)

  4. 1. Insert (cont) • Fetch data from existing tables. Create a table (sales) that contains the sales amount of each agent • 1. Create a new table “Sales” • create table Sales • (aid char(3) not null, • total money • ); • 2. Load data into table “Sales” • Insert into Sales • select aid, sum(dollars) from orders group by aid

  5. 1. Insert (cont) • Insert [into] tablename [column name, …] values (expr | null, …) | subquery

  6. 2. Update • Raise all products price by 10% • Update products set price = price*1.1 • Increase customer c001’s discount from 10% to 15% • Update customers set discnt = 15 where cid = ‘c001’

  7. 2. Update (cont) • Give all customers who have ordered more than $800 a 11% increase in the discount • Select cid from orders group by cid having sum(dollars) > 800 • Update customers set discnt = 1.11*discnt where cid in (Select cid from orders group by cid having sum(dollars) > 800)

  8. 2. Update (cont) • Correlated update • create table NECustomers ( cid char(4) not null, cname varchar(15), city varchar(15), discnt decimal(10,2), primary key(cid) ); • Insert NECustomers select * from customers where city = ‘Boston’

  9. 2. Update (cont) • Correlated update • Update the discount in NECustomers table with the discount from customers table • Update NECustomers set discnt = (select discnt from customers where cid = NECustomers.cid)

  10. 2. Update (cont) • Update tablename set column_name = {expr | null | (subquery)} [where search condition]

  11. 3. Delete • Delete all customers from Boston • Delete from customers where city = ‘Boston’

  12. 3. Delete (cont) • Delete all agents whose total sales amount is less than $500 • Delete from agents where aid in (select aid from orders group by aid having sum(dollars) < 500)

  13. 3. Delete (cont) • Delete from table [where search_condition]

  14. 4. Create table • Create customers table • create table Customers ( cid char(4) not null, cname varchar(15), city varchar(15), discnt decimal(10,2), primary key(cid) ); • All data types

  15. 4. Create table (cont) Key words Not null Primary Key Default Check Constraint Foreign Key

  16. 4. Create table (cont) • drop table people • create table People • (SSN char(9) not null, • fname varchar(10), • lname varchar(10) not null, • age smallint constraint age_min check (age >= 16), • salary money default 1000, • branchno char(2) default 'MA', • primary key(SSN) • );

  17. 4. Create table (cont) • insert people (SSN) values ('018564356') • insert people (SSN,fname,lname,age) values ('018564356','Scott','Miller',15) • insert people (SSN,fname,lname,age) values ('018564356','Scott','Miller',16)

  18. 4. Create table (cont) • drop table People_experience • create table People_experience • (eid char(4) not null, • SSN char(9) not null, • company_name varchar(10), • start_date datetime, • position varchar(10), • primary key(eid), • constraint SSN_ref foreign key (SSN) references people • );

  19. 4. Create table (cont) • insert People_experience (eid, SSN, company_name) values ('c001', '011223333', 'Google') • insert People_experience (eid, SSN, company_name) values ('c001', '018564356', 'Google')

  20. 5. Alter table • Alter table people_experience add location varchar (20) not null • Alter table people_experience add location varchar (20) • Alter table people_experience drop column location

More Related