SQL/MySQL/Insert Delete Update/Delete — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:16, 26 мая 2010
Содержание
Delete by JOIN
/*
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.00 sec)
mysql> DELETE Orders.*
-> FROM Books, Orders
-> WHERE Books.BookID=Orders.BookID
-> AND Books.BookName="News";
Query OK, 3 rows affected (0.05 sec)
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
17 rows in set (0.00 sec)
*/
Drop table Books;
Drop table Orders;
CREATE TABLE Books
(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookName VARCHAR(40) NOT NULL,
InStock SMALLINT NOT NULL
)
ENGINE=INNODB;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"),
(1002, 101, 1, "2001-02-12 12:31:00"),
(1003, 103, 2, "2002-03-12 12:34:00"),
(1004, 104, 3, "2003-04-12 12:36:00"),
(1005, 102, 1, "2004-05-12 12:41:00"),
(1006, 103, 2, "2001-06-12 12:59:00"),
(1007, 101, 1, "2002-07-12 13:01:00"),
(1008, 103, 1, "2003-08-12 13:02:00"),
(1009, 102, 4, "2004-09-12 13:22:00"),
(1010, 101, 2, "2005-11-12 13:30:00"),
(1011, 103, 1, "2006-12-12 13:32:00"),
(1012, 105, 1, "2001-02-12 13:40:00"),
(1013, 106, 2, "2002-04-12 13:44:00"),
(1014, 103, 1, "2003-06-12 14:01:00"),
(1015, 106, 1, "2005-01-12 14:05:00"),
(1016, 104, 2, "2003-11-12 14:28:00"),
(1017, 105, 1, "2002-03-12 14:31:00"),
(1018, 102, 1, "2001-05-12 14:32:00"),
(1019, 106, 3, "2003-07-12 14:49:00"),
(1020, 103, 1, "2004-01-12 14:51:00");
INSERT INTO Books VALUES (101, "Writing", 12),
(102, "News", 17),
(103, "Angels", 23),
(104, "Poet", 32),
(105, "Dunces", 6),
(106, "Solitude", 28);
select * from Orders;
DELETE Orders.*
FROM Books, Orders
WHERE Books.BookID=Orders.BookID
AND Books.BookName="News";
select * from Orders;
Delete row with condition
/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title | age | yearofservice| salary | perks | email |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru |
| 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru |
| 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru |
| 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru |
| 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru |
| 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru |
| 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru |
| 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru |
| 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru |
| 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru |
| 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru |
| 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru |
| 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru |
| 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru |
| 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru |
| 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru |
| 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru |
| 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru |
| 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru |
| 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.02 sec)
mysql> DELETE from employee
-> WHERE id = 10;
Query OK, 1 row affected (0.00 sec)
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title | age | yearofservice| salary | perks | email |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru |
| 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru |
| 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru |
| 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru |
| 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru |
| 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru |
| 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru |
| 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru |
| 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru |
| 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru |
| 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru |
| 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru |
| 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru |
| 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru |
| 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru |
| 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru |
| 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru |
| 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru |
| 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
19 rows in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
id int unsigned not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20),
title varchar(30),
age int,
yearofservice int,
salary int,
perks int,
email varchar(60)
);
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
DELETE from employee
WHERE id = 10;
select * from employee;
Delete with JOIN 2
Drop table Books;
Drop table Orders;
CREATE TABLE Books
(
BookID SMALLINT NOT NULL PRIMARY KEY,
BookName VARCHAR(40) NOT NULL,
InStock SMALLINT NOT NULL
)
ENGINE=INNODB;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"),
(1002, 101, 1, "2001-02-12 12:31:00"),
(1003, 103, 2, "2002-03-12 12:34:00"),
(1004, 104, 3, "2003-04-12 12:36:00"),
(1005, 102, 1, "2004-05-12 12:41:00"),
(1006, 103, 2, "2001-06-12 12:59:00"),
(1007, 101, 1, "2002-07-12 13:01:00"),
(1008, 103, 1, "2003-08-12 13:02:00"),
(1009, 102, 4, "2004-09-12 13:22:00"),
(1010, 101, 2, "2005-11-12 13:30:00"),
(1011, 103, 1, "2006-12-12 13:32:00"),
(1012, 105, 1, "2001-02-12 13:40:00"),
(1013, 106, 2, "2002-04-12 13:44:00"),
(1014, 103, 1, "2003-06-12 14:01:00"),
(1015, 106, 1, "2005-01-12 14:05:00"),
(1016, 104, 2, "2003-11-12 14:28:00"),
(1017, 105, 1, "2002-03-12 14:31:00"),
(1018, 102, 1, "2001-05-12 14:32:00"),
(1019, 106, 3, "2003-07-12 14:49:00"),
(1020, 103, 1, "2004-01-12 14:51:00");
INSERT INTO Books VALUES (101, "Writing", 12),
(102, "News", 17),
(103, "Angels", 23),
(104, "Poet", 32),
(105, "Dunces", 6),
(106, "Solitude", 28);
select * from Orders;
DELETE FROM Orders
USING Books, Orders
WHERE Books.BookID=Orders.BookID
AND Books.BookName="Poet";
select * from Orders;
Deleting Rows with DELETE
/* Create the table */
Drop TABLE Professor;
CREATE TABLE Professor (
ProfessorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL)
TYPE = InnoDB;
/* Prepare the data */
INSERT INTO Professor (ProfessorID,Name) VALUES (1,"John Jones");
INSERT INTO Professor (ProfessorID,Name) VALUES (2,"Cury Butz");
INSERT INTO Professor (ProfessorID,Name) VALUES (3,"JJ Smith");
/* Real command */
DELETE FROM Professor WHERE ProfessorID > 2;
Using DELETE LOW_PRIORITY command
/*
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1011 | 103 | 1 | 2006-12-12 13:32:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
20 rows in set (0.01 sec)
mysql> DELETE LOW_PRIORITY FROM Orders
-> WHERE BookID=103
-> ORDER BY DateOrdered DESC
-> LIMIT 1;
Query OK, 1 row affected (0.03 sec)
mysql> select * from Orders;
+---------+--------+----------+---------------------+
| OrderID | BookID | Quantity | DateOrdered |
+---------+--------+----------+---------------------+
| 1001 | 103 | 1 | 2004-01-12 12:30:00 |
| 1002 | 101 | 1 | 2001-02-12 12:31:00 |
| 1003 | 103 | 2 | 2002-03-12 12:34:00 |
| 1004 | 104 | 3 | 2003-04-12 12:36:00 |
| 1005 | 102 | 1 | 2004-05-12 12:41:00 |
| 1006 | 103 | 2 | 2001-06-12 12:59:00 |
| 1007 | 101 | 1 | 2002-07-12 13:01:00 |
| 1008 | 103 | 1 | 2003-08-12 13:02:00 |
| 1009 | 102 | 4 | 2004-09-12 13:22:00 |
| 1010 | 101 | 2 | 2005-11-12 13:30:00 |
| 1012 | 105 | 1 | 2001-02-12 13:40:00 |
| 1013 | 106 | 2 | 2002-04-12 13:44:00 |
| 1014 | 103 | 1 | 2003-06-12 14:01:00 |
| 1015 | 106 | 1 | 2005-01-12 14:05:00 |
| 1016 | 104 | 2 | 2003-11-12 14:28:00 |
| 1017 | 105 | 1 | 2002-03-12 14:31:00 |
| 1018 | 102 | 1 | 2001-05-12 14:32:00 |
| 1019 | 106 | 3 | 2003-07-12 14:49:00 |
| 1020 | 103 | 1 | 2004-01-12 14:51:00 |
+---------+--------+----------+---------------------+
19 rows in set (0.00 sec)
*/
Drop table Orders;
CREATE TABLE Orders
(
OrderID SMALLINT NOT NULL PRIMARY KEY,
BookID SMALLINT NOT NULL,
Quantity TINYINT (40) NOT NULL DEFAULT 1,
DateOrdered TIMESTAMP,
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"),
(1002, 101, 1, "2001-02-12 12:31:00"),
(1003, 103, 2, "2002-03-12 12:34:00"),
(1004, 104, 3, "2003-04-12 12:36:00"),
(1005, 102, 1, "2004-05-12 12:41:00"),
(1006, 103, 2, "2001-06-12 12:59:00"),
(1007, 101, 1, "2002-07-12 13:01:00"),
(1008, 103, 1, "2003-08-12 13:02:00"),
(1009, 102, 4, "2004-09-12 13:22:00"),
(1010, 101, 2, "2005-11-12 13:30:00"),
(1011, 103, 1, "2006-12-12 13:32:00"),
(1012, 105, 1, "2001-02-12 13:40:00"),
(1013, 106, 2, "2002-04-12 13:44:00"),
(1014, 103, 1, "2003-06-12 14:01:00"),
(1015, 106, 1, "2005-01-12 14:05:00"),
(1016, 104, 2, "2003-11-12 14:28:00"),
(1017, 105, 1, "2002-03-12 14:31:00"),
(1018, 102, 1, "2001-05-12 14:32:00"),
(1019, 106, 3, "2003-07-12 14:49:00"),
(1020, 103, 1, "2004-01-12 14:51:00");
select * from Orders;
DELETE LOW_PRIORITY FROM Orders
WHERE BookID=103
ORDER BY DateOrdered DESC
LIMIT 1;
select * from Orders;
Using the "from join delete" Alternative to Delete Data
Drop table DVDs;
Drop table Studios;
CREATE TABLE DVDs
(
DVDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
DVDName VARCHAR(60) NOT NULL,
NumDisks TINYINT NOT NULL DEFAULT 1,
YearRlsd YEAR NOT NULL,
StudID VARCHAR(4) NOT NULL
) ENGINE=INNODB;
CREATE TABLE Studios
(
StudID VARCHAR(4) NOT NULL,
StudDescrip VARCHAR(40) NOT NULL,
PRIMARY KEY (StudID)
)
ENGINE=INNODB;
INSERT INTO Studios VALUES ("s101", "Universal Studios"),
("s102", "Warner Brothers"),
("s103", "Time Warner"),
("s104", "Columbia Pictures"),
("s105", "Paramount Pictures"),
("s106", "Twentieth Century Fox"),
("s107", "Merchant Ivory Production");
INSERT INTO DVDs
(DVDName, NumDisks, YearRlsd, StudID)
VALUES
("Christmas", 1, 2000, "s105"),
("What", 1, 2001, "s103"),
("Out", 1, 2000, "s101"),
("Falcon", 1, 2000, "s103"),
("Amadeus", 1, 1997, "s103"),
("Show", 2, 2000, "s106"),
("View", 1, 2000, "s107"),
("Mash", 2, 2001, "s106");
select * from DVDs;
DELETE DVDs
FROM DVDs, Studios
WHERE DVDs.StudID=Studios.StudID
AND Studios.StudDescrip="New Line Cinema";
select * from DVDs;