1 / 10

SQL in Access

SQL in Access. S511. Create Table Schema. CREATE TABLE student ( student_id INTEGER NOT NULL, name CHAR(25), major CHAR(10), gpa INTEGER, CONSTRAINT index1 PRIMARY KEY(student_id)); CREATE TABLE course ( course_id CHAR(15), name CHAR(25), department_id CHAR(10),

elizabeth-savage
Download Presentation

SQL in Access

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 in Access S511

  2. Create Table Schema CREATE TABLE student ( student_id INTEGER NOT NULL, name CHAR(25), major CHAR(10), gpa INTEGER, CONSTRAINT index1 PRIMARY KEY(student_id)); CREATE TABLE course ( course_id CHAR(15), name CHAR(25), department_id CHAR(10), CONSTRAINT index1 PRIMARY KEY (course_id) ); CREATE TABLE enroll ( student_id INTEGER, course_id CHAR(15), grade CHAR(2), CONSTRAINT index1 PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES student(student_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );

  3. Add instances to tables INSERT INTO student VALUES (101, 'Bill', 'CIS', 3.45); INSERT INTO student VALUES (102, 'Mary', 'CIS', 3.10); INSERT INTO student VALUES (103, 'Sue', 'MKT', 3.90); INSERT INTO course VALUES ('CIS3100', 'Database', 'CIS'); INSERT INTO course VALUES ('CIS3400', 'Network I', 'CIS'); INSERT INTO course VALUES ('CIS3500', 'Network II', 'CIS'); INSERT INTO course VALUES ('MKT3000', 'Advertizing', 'MKT'); INSERT INTO course VALUES ('MKT3200', 'Marketing I', 'MKT'); INSERT INTO course VALUES ('MKT4200', 'Marketing II', 'MKT'); INSERT INTO enroll VALUES (101, 'CIS3100', 'A'); INSERT INTO enroll VALUES (101, 'CIS3500', 'B+'); INSERT INTO enroll VALUES (102, 'CIS3100', 'A-'); INSERT INTO enroll VALUES (102, 'CIS3400', 'A'); INSERT INTO enroll VALUES (103, 'MKT3000', 'A'); INSERT INTO enroll VALUES (103, 'MKT3200', 'B'); INSERT INTO enroll VALUES (103, 'MKT4200', 'B+');

  4. Example

  5. SQL: Creating/Dropping table • Create Table CREATE TABLE student1( student_id INTEGER NOT NULL, name CHAR(25), major CHAR(10), gpa INTEGER, CONSTRAINT index2 PRIMARY KEY(student_id)); • Drop table DROP TABLE student1;

  6. Modifying table data INSERT INTO student VALUES (104, 'Ying', 'SLIS', 4); SELECT * FROM student; UPDATE student SET student.name = "Ding" WHERE ((student.student_id=104)); DELETE FROM student WHERE student_id=104;

  7. Altering tables ALTER TABLE student ADD Available CHAR(1); ALTER TABLE student DROP Available;

  8. Queries SELECT * FROM course; SELECT * FROM enroll WHERE grade=“A”; SELECT * FROM student WHERE student.student_id=(SELECT enroll.student_id FROM enroll WHERE grade=“A-”); SELECT student.name FROM student, enroll WHERE student.student_id=enroll.student_id AND enroll.grade=“A”;

  9. Sorting and Grouping SELECT * FROM enroll ORDER BY grade, course_id; SELECT name, max(gpa) FROM student GROUP BY name HAVING max(gpa)>3; SELECT DISTINCT grade FROM enroll;

  10. Joining tables SELECT student.name, enroll.course_id, enroll.grade FROM student INNER JOIN enroll ON student.student_id=enroll.student_id; SELECT * FROM student LEFT JOIN enroll ON student.student_id=enroll.student_id; SELECT * FROM student RIGHT JOIN enroll ON student.student_id=enroll.student_id;

More Related