Create and manage tables. Write sub-queries

Structured Query Language for Data Management (CS362)

DB4

Unit 1 – Individual Project

5-21-17

Installation Issues:

I was a little worried after watching the first link under the announcements section on the portal because it was all about MACS. I watched the whole thing and was trying to figure out how I was going to install this on my PC as I’ve never used anything MAC. I went to the install page and downloaded the MySQL Installer for Windows. I think I installed everything in the entire suite and thought to myself that it was a lot of stuff I would never use. I then viewed the video installation instructions link for Sachin Samy’s YouTube video under the assignment details. This was what I needed! I uninstalled everything I had previously installed and followed the instructions from his video. The video felt a little slow while having to listen to him, but I was able to get everything installed and running.

Unit 2 – Individual Project

5-28-17

Here is the script that I used to create the tables within my database. I have also attached a screen shot of the diagram which shows how the tables relate to each other.

CREATE TABLE [dbo].[Advisors](

[AdvisorId] [int] IDENTITY(1,1) NOT NULL,  [FirstName] [varchar](30) NOT NULL,  [LastName] [varchar](30) NOT NULL,  [EmailAddr] [varchar](100) NOT NULL,

PRIMARY KEY CLUSTERED  (

[AdvisorId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  ) ON [PRIMARY]  GO

CREATE TABLE [dbo].[Students](

[StudentId] [int] IDENTITY(10,500) NOT NULL,  [FirstName] [varchar](30) NOT NULL,  [LastName] [varchar](30) NOT NULL,  [Birthdate] [smalldatetime] NOT NULL,  [Gender] [char](1) NOT NULL,  [StartDate] [smalldatetime] NOT NULL,  [GPA] [decimal](3, 2) NOT NULL,  [IsActive] [bit] NOT NULL,  [Bio] [text] NOT NULL,  [AdvisorId] [int] NOT NULL,

CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED  (

[StudentId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO  ALTER TABLE [dbo].[Students] WITH CHECK ADD CONSTRAINT [FK_Students_Advisors] FOREIGN KEY([AdvisorId])  REFERENCES [dbo].[Advisors] ([AdvisorId])  GO  ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [FK_Students_Advisors]  GO

CREATE TABLE [dbo].[Classes](

[ClassId] [int] IDENTITY(1,1) NOT NULL,  [ClassCode] [varchar](50) NOT NULL,  [ClassName] [varchar](100) NOT NULL,  [Description] [varchar](2000) NOT NULL,

CONSTRAINT [PK_Classes] PRIMARY KEY CLUSTERED  (

[ClassId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  ) ON [PRIMARY]  GO

CREATE TABLE [dbo].[Students_Classes](

[StudentClassId] [int] IDENTITY(1,1) NOT NULL,  [StudentId] [int] NOT NULL,  [ClassId] [int] NOT NULL,  [StartDate] [datetime] NOT NULL,  [Assignment1] [smallint] NOT NULL,  [Assignment2] [smallint] NOT NULL,  [Assignment3] [smallint] NOT NULL,  [Assignment4] [smallint] NOT NULL,  [GPA] [numeric](3, 2) NOT NULL,

CONSTRAINT [PK_Students_Classes] PRIMARY KEY CLUSTERED  (  [StudentClassId] ASC  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  ) ON [PRIMARY]

GO  ALTER TABLE [dbo].[Students_Classes] WITH CHECK ADD CONSTRAINT [FK_Students_Classes_Classes] FOREIGN KEY([ClassId])  REFERENCES [dbo].[Classes] ([ClassId])  GO  ALTER TABLE [dbo].[Students_Classes] CHECK CONSTRAINT [FK_Students_Classes_Classes]  GO  ALTER TABLE [dbo].[Students_Classes] WITH CHECK ADD CONSTRAINT [FK_Students_Classes_Students] FOREIGN KEY([StudentId])  REFERENCES [dbo].[Students] ([StudentId])  GO  ALTER TABLE [dbo].[Students_Classes] CHECK CONSTRAINT [FK_Students_Classes_Students]  GO

Unit 3 – Individual Project

The following commands were important as it allowed me to quickly insert and manipulate data with my database. Upon execution, I could review the messages and correct any errors that occurred. I had to correct errors in parts 2 and 5 of this assignment. I have provided screen shots of the code execution and the effect it had on my database.

Part 1:

Created ‘New Query’ and executed the following script:

INSERT INTO Classes (ClassCode, ClassName, Description)

VALUES ('ACCT306', 'Accounting 1', 'Introduces accounting concepts and explores the accounting environment. Covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. Also introduces the concept of internal control and how to account for assets.')

INSERT INTO Classes (ClassCode, ClassName, Description)

VALUES ('CS362', 'Structured Query Language for Data Management', 'This course gives complete coverage of SQL, with an emphasis on storage, retrieval and manipulation of data.')

INSERT INTO Classes (ClassCode, ClassName, Description)

VALUES ('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.')

INSERT INTO Classes (ClassCode, ClassName, Description)

VALUES ('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.')

Result of Part 1:

Part 2:

Created ‘New Query’ and executed the following script:

INSERT INTO Advisors (FirstName, LastName, EmailAddr)

VALUES ('Fred', 'Stone', '[email protected]')

INSERT INTO Advisors (FirstName, LastName, EmailAddr)

VALUES ('Bob', 'Gordon', '[email protected]')

INSERT INTO Advisors (FirstName, LastName, EmailAddr)

VALUES ('Jack', 'Simpson', '[email protected]')