SQL

After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information. Notice that the values for the primary key columns (ClassId in the Classes table, AdvisorId in the Advisors table and StudentId in the Students table) are not listed. It is assumed that they will be “Identity” or “Auto-increment” columns and the DBMS will determine the proper identity value when the row is inserted.

You may have to either modify your original data types for some of your table columns or modify the values below to fit your data types. For example, if you defined your gender column to be a 1-character column, you will need to use the values M or F instead of Male and Female. The IsActive column is another column that may require different values, depending on the type of DBMS used.

1. Insert the following classes’ records:

Code Name
Description
ACCT306 Accounting 1 This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.
CS362 Structured Query Language for Data Management This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.
ENG115 English Composition In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.
FIN322 Investments This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.
2. Insert the following advisors’ records:

Name Email
Fred Stone [email protected]
Bob Gordon [email protected]
Jack Simpson [email protected]
3. Insert the following students’ records:

Name Birthdate Gender StartDate GPA IsActive Bio AdvisorID
Craig Franklin 1970-03-15 Male 2010-05-30 3.10 Yes 3
Harriet Smith 1982-04-15 Female 2010-05-30 3.22 Yes 1
George David 1984-11-05 Male 2010-10-01 0.00 Yes 3
Ben Jefferson 1976-09-25 Male 2009-02-21 1.80 No The student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year. 3
4. Delete the course named Investments from the system.

5. Change Harriet Smith’s birthdate to April 25, 1982 and her GPA to 3.25.

SQL

After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information. Notice that the values for the primary key columns (ClassId in the Classes table, AdvisorId in the Advisors table and StudentId in the Students table) are not listed. It is assumed that they will be “Identity” or “Auto-increment” columns and the DBMS will determine the proper identity value when the row is inserted.

You may have to either modify your original data types for some of your table columns or modify the values below to fit your data types. For example, if you defined your gender column to be a 1-character column, you will need to use the values M or F instead of Male and Female. The IsActive column is another column that may require different values, depending on the type of DBMS used.

1. Insert the following classes’ records:

Code Name
Description
ACCT306 Accounting 1 This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.
CS362 Structured Query Language for Data Management This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.
ENG115 English Composition In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.
FIN322 Investments This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.
2. Insert the following advisors’ records:

Name Email
Fred Stone [email protected]
Bob Gordon [email protected]
Jack Simpson [email protected]
3. Insert the following students’ records:

Name Birthdate Gender StartDate GPA IsActive Bio AdvisorID
Craig Franklin 1970-03-15 Male 2010-05-30 3.10 Yes 3
Harriet Smith 1982-04-15 Female 2010-05-30 3.22 Yes 1
George David 1984-11-05 Male 2010-10-01 0.00 Yes 3
Ben Jefferson 1976-09-25 Male 2009-02-21 1.80 No The student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year. 3
4. Delete the course named Investments from the system.

5. Change Harriet Smith’s birthdate to April 25, 1982 and her GPA to 3.25.