Assign #6 Dentist Tables (4) Assign #7 Tables (4) CIST 1220: SQL – Assign #8 (40

Question

                            

Assign #6 Dentist Tables (4)

Assign #7 Tables (4)

CIST 1220: SQL – Assign #8

(40 Points)

[INSERTs/DELETEs/UPDATEs – Chap 10]

For this assignment, use your Dentist Office tables you created in Assign #6. Use just the 4 Dentist Office tables

INSERT : Using SQL, Insert 4 rows of data to each of the Dentist Office tables that you created in Assign #7. Just make up the data that you add. You should have at least 16 SQL Insert statements to hand in. Print out these SQL statements to hand them in.

DELETE: Next, again using SQL, Delete the last 2 rows in each table from your Database. Use SQL Delete statements. Delete using a where clause based on the Primary key in each table. Print these SQL statements and hand them in. You should have at least 8 Delete statements.

UPDATE: Lastly, change all the lastnames in your “Dentists” table to “Moore”. Also change the firstname of the last row of data in each possible table, to “Frank”. Change any zipcodes from CA to 90000.    You should have at least 3 update statements.

DROP Table: Finally, remove the entire Procedures Table.

Courses CourseID CourseName ption reditHou Intro to Computers 4 ST 1001 Computer Concepts IST 1122 Hardware Maintainence Course Hardware Maintainence IST 1130 IST 1220 SQL Programming IST 1305Program Design and Development Intro to Programming IST 1401 IST 1510 Web Development I ST 2341 C# Programming I IST 2342|C# Programming 11 IST 2371 Java Programming I IST 2372 Java Programming II IST 2921 Analysis and Design IST 2931Systems Project IST 3273Java Programming III ing Systems plore how an Operating System works 4 ing about SQL Networking Fundamentals Intro to C# Programming rking Fundamentals evelop Web-pages using HTML Advanced C# Programming Intro to Java Intermediate Java Topics bject Oriented Analysis and Design apstone Project Class sing Java to build Web Applications

Explanation / Answer

CREATE TABLE Employees (
empID int primary key,
firstName varchar(20),
lastName varChar(20),
address varchar(100),
deptCode varchar(10);
salary int;
email varchar(30);
);

CREATE TABLE Depts (
deptID int primary key,
DeptName varchar(20),
DeptLocation varChar(20),
);

CREATE TABLE Accounts (
acctNo int,
owner varchar(20),
address varChar(20),
type varchar(10),
salary double;
);

Insert/update/delete

1-
INSERT INTO Courses (CourseID,CourseName,Description,CreditHours)
VALUES ('CIST 1002','C programming','Intro to c programming language',7);

INSERT INTO Courses (CourseID,CourseName,Description,CreditHours)
VALUES ('CIST 1111','Perl programming','Intro to Perl programming language',10);

INSERT INTO Courses (CourseID,CourseName,Description,CreditHours)
VALUES ('CIST 1112','Pyhton programming','Intro to Python programming language',6);

INSERT INTO Courses (CourseID,CourseName,Description,CreditHours)
VALUES ('CIST 2800','R programming','Intro to R programming language',15);

INSERT INTO Instructors (ID,FirstName,LastName,Street,City,State,Zip,Office,EMail)
VALUES (7,'Joe','sanders','121 park street','Sanfransisco','USA','234567','A10101','Joe@abc.com');

INSERT INTO Instructors (ID,FirstName,LastName,Street,City,State,Zip,Office,EMail)
VALUES (8,'fransis','gomez','131 park street','Sanfransisco','USA','234567','B10101','fransis@abc.com');

INSERT INTO Instructors (ID,FirstName,LastName,Street,City,State,Zip,Office,EMail)
VALUES (9,'Dianne','Dixon','122 park street','Sanfransisco','USA','234567','C10101','Dianne@abc.com');

INSERT INTO Instructors (ID,FirstName,LastName,Street,City,State,Zip,Office,EMail)
VALUES (10,'Travis','givens','140 park street','Sanfransisco','USA','234567','D10101','Travis@abc.com');

INSERT INTO Sections (CRN,CourseID,TimeDays,RoonNo,Instructor)
VALUES (30117,'CIST 2341','MW1-6pm','F1145',2);

INSERT INTO Sections (CRN,CourseID,TimeDays,RoonNo,Instructor)
VALUES (30118,'CIST 1001','TTH6-9pm','F1144',5);

INSERT INTO Sections (CRN,CourseID,TimeDays,RoonNo,Instructor)
VALUES (30119,'CIST 2341','MW1-6pm','F1145',3);

INSERT INTO Sections (CRN,CourseID,TimeDays,RoonNo,Instructor)
VALUES (30120,'CIST 1130','MW8-11am','F1144',2);

INSERT INTO Students (ID,FirstName,LastName,Street,City,State,Zip,EMail,GPA)
VALUES (17,'Travis','givens','140 park street','Sanfransisco','USA','234567','Travis@abc.com',6.5);

INSERT INTO Students (ID,FirstName,LastName,Street,City,State,Zip,EMail,GPA)
VALUES (18,'Paul','givens','140 park street','Sanfransisco','USA','234567','Paul@abc.com',5.4);

INSERT INTO Students (ID,FirstName,LastName,Street,City,State,Zip,EMail,GPA)
VALUES (19,'Sean','paul','141 park street','Sanfransisco','USA','234567','sean@abc.com',5.5);

INSERT INTO Students (ID,FirstName,LastName,Street,City,State,Zip,EMail,GPA)
VALUES (20,'Joe','burns','141 park street','Sanfransisco','USA','234567','Joe@abc.com',7.5);

DELETE FROM Courses WHERE CourseID='CIST1001';
DELETE FROM Courses WHERE CourseID='CIST1130';

DELETE FROM Instructors WHERE ID=1;
DELETE FROM Instructors WHERE ID=2;

DELETE FROM Sections WHERE CRN=30101;
DELETE FROM Sections WHERE CRN=30102;

DELETE FROM Students WHERE ID=3;
DELETE FROM Students WHERE ID=4;

UPDATE Dentists
SET LastName = 'Morre';

UPDATE Students
SET zip = '90000'
WHERE State='CA';

DROP Procedures;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.