Create and manage tables. Write queries to solve complex information requests

I contracted to design and develop a database for CTU that will store individual and confidential university data. This database is required to give the back-end engineering to a front-end web application with an instinctive User/Interface (U/I) to be utilized by the college HR office. We've chosen to utilize Microsoft SQL Server 2012 given the way of information to be put away because it will be more secure, and it additionally gives a suite of server upkeep apparatuses to be deserted with the IT Department once the database and web application have been tried and acknowledged by college partners.

Amid our preparatory gatherings, CTU's necessities were characterized and enough perused to start making of the database. The accompanying areas contain the business tenets and element tables created amid the preparatory gatherings, and additionally duplicates of all the SQL code used to manufacture the database and make the Entity Relationship Diagram (ERD).

· A student has a name, a birth date, and gender.

· You must track the date the student started at the university and his or her current GPA, as well as be able to inactivate him or her without deleting information.

· For advising purposes, store the student's background/bio information. This is like a little story.

· An advisor has a name and an e-mail address.

· Students are assigned to one advisor, but one advisor may service multiple students.

· A class has a class code, name, and description.

· You need to indicate the specific classes a student is taking/has taken at the university. Track the date the student started a specific class and the grade earned in that class.

· Each class that a student takes has 4 assignments. Each assignment is worth 100 points.

Create Database:

CREATE DATABASE [Cameron_CTU]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'Cameron_CTU', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SCAMERON_CTU\MSSQL\DATA\Cameron_CTU.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Cameron_CTU_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL11.SCAMERON_CTU\MSSQL\DATA\Cameron_CTU_log.ldf' , SIZE = 1024KB , FILEGROWTH = 01% )

GO

ALTER DATABASE [Cameron_CTU] SET COMPATIBILITY_LEVEL = 110

GO

ALTER DATABASE [Cameron_CTU] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [Cameron_CTU] SET ANSI_NULLS OFF

GO

ALTER DATABASE [Cameron_CTU] SET ANSI_PADDING OFF

GO

ALTER DATABASE [Cameron_CTU] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [Cameron_CTU] SET ARITHABORT OFF

GO

ALTER DATABASE [Cameron_CTU] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [Cameron_CTU] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [Cameron_CTU] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [Cameron_CTU] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [Cameron_CTU] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [Cameron_CTU] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [Cameron_CTU] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [Cameron_CTU] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [Cameron_CTU] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [Cameron_CTU] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [Cameron_CTU] SET DISABLE_BROKER

GO

ALTER DATABASE [Cameron_CTU] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [Cameron_CTU] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [Cameron_CTU] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [Cameron_CTU] SET READ_COMMITTED_SNAPSHOT OFF

GO

ALTER DATABASE [Cameron_CTU] SET READ_WRITE

GO

ALTER DATABASE [Cameron_CTU] SET RECOVERY SIMPLE

GO

ALTER DATABASE [Cameron_CTU] SET MULTI_USER

GO

ALTER DATABASE [Cameron_CTU] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [Cameron_CTU] SET TARGET_RECOVERY_TIME = 0 SECONDS

GO

USE [Cameron_CTU]

GO

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Cameron_CTU] MODIFY FILEGROUP [PRIMARY] DEFAULT

GO

Create Database:

Create Students Table with FOREIGN KEYS:

Create Advisors Table:

Create Classes Table:

Alter [Students_Classes] & [Students] Tables to assign Foreign Keys to them, which are PRIMARY KEYS on the referenced tables. Once the relationships have been established, the ERD can be created

New DML script to add required constraints for GPA, Gender, and Assignments1 – Assignments4

New Database Diagram: ERD

Carrying on from Phase 1 undertakings of making our college database, with the four tables of Students, Advisors, Classes, and Students_Classes, Phase 2 calls for information to be embedded, erased, and refreshed. The INSERT articulation has two approaches to enter. The positional embed embeds requested esteems into another line in a similar request of the table segments. A named-section embed names the correct segment where each esteem's embedded into another line. Its great practice to utilize a named-segment embed, which permits your SQL code to work if the table's sections are reordered or another segment is included. You would utilize INSERT VALUES to determine segment esteems, and INSERT SELECT to embed columns from another table. Some DBMS don't make it obligatory to utilize the INTO watchword after the INSERT articulation; be that as it may, you ought to utilize it for its transportability abilities. (Fehily, 2008)

The UPDATE explanation is utilized to change esteems in a table's current columns. You should be extremely careful when utilizing the refresh explanation on the grounds that on the off chance that you neglect to include the "WHERE" statement, you will refresh all lines rather than simply your objective columns. Keep in mind that each refreshed esteem needs to have similar information sort or be absolutely convertible to an indistinguishable sort from its segment. (Fehily, 2008)

The DELETE explanation is somewhat more capable in light of the fact that it’s used to erase a whole line, not just a segment or sections. You can erase only one or all columns of a table, so be mindful of the erase explanation moreover. Without the WHERE proviso, every single table column will be erased. (Fehily, 2008)

The first task was to insert data into four rows of the Classes table. The following SQL code is what I used to accomplish the task, followed by the screenshot of the query being executed successfully.

INSERT INTO [Cameron_CTU].[DBO].[Classes]

( [ClassCode], [ClassName], [Description] )

VALUES

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

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

(Fehily, 2008)

The second task was to insert data into three rows of the Advisors table. The following SQL code is what I used to accomplish the task, followed by the screenshot of the query being executed successfully.

INSERT INTO [dbo].[Advisors]

( [FirstName],

[LastName],

[EmailAddr] )

VALUES

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

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

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

(Fehily, 2008)

The third task was to insert data into four rows of the Students table. The following SQL code is what I used to accomplish the task, followed by the screenshot of the query being executed successfully.

INSERT INTO [dbo].[Students]

( [FirstName],

[LastName],

[BirthDate],

[Gender],

[StartDate],

[GPA],

[IsActive],

[Bio],

[AdvisorID] )

VALUES

( 'Craig', 'Franklin', '1970-03-15', 'm', '2010-05-30', 3.10, 'Yes', '', 3 ),

( 'Harriet', 'Smith', '1982-04-15', 'f', '2010-05-30', 3.22, 'Yes', '', 1 ),

( 'George', 'David', '1984-11-05', 'm', '2010-10-01', 0.00, 'Yes', '', 3 ),

( 'Ben', 'Jefferson', '1976-09-25', 'm', '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 )

(Fehily, 2008)

The fourth task was to delete the course named Investments from the system, and the following SQL code is what I used to accomplish the task, followed by the screenshot of the query being executed successfully.

DELETE FROM Classes

WHERE ClassID = 'FIN322';

(Fehily, 2008)

The fifth and final task called for changing 2 columns in a row on the Students table, and the following SQL code is what I used to accomplish the task, followed by the screenshot of the query being executed successfully.

UPDATE Students

SET BirthDate = '1982-04-25',

GPA = 3.25

WHERE StudentID = '2';

(Fehily, 2008)

Stage 3 has seven errands that call for recovering information utilizing the SELECT and FROM Statements. The SELECT condition calls for which section or segments to show, and the FROM proviso distinguishes the table or tables the segments have a place with. You can likewise utilize the AS statement with a specific end goal to make segment assumed names, which I'll go over in the last couple of errands. You can likewise sort columns with the ORDER BY condition, which I'll additionally hit on in the assignments. The WHERE provision channels undesirable columns. Without it, you result would be each column on your questioned table. Some other proclamation conditions incorporate AND, OR, ORDER BY GROUP BY, JOIN. There are two sorts of JOIN provisos. They are Implicit and express. Certain is utilized a considerable measure, and you may not understand your utilizing it because there is no genuine JOIN proviso with JOIN in it; its suggested when you're questioning more than one table. Let’s begin with the assignments now, might we?

(Fehily, 2008)

The main thing will do is fix my SQL proclamation in the Phase 2 segment, Task 5. I settled it by utilizing StudentID in the SELECT proviso.

The first task calls for a list of all active male students assigned to Advisors 1 or 3.

So, we want to query the Advisor. Advisor ID, FirstName and LastName columns along with the Students. AdvisorID, FirstName, LastName, Birthdate, Gender, and GPA columns of the Students and Advisors tables. We use an implicit JOIN with the WHERE clause to JOIN the two tables of Student. AdvisorID and Advisor. AdvisorID together. Below, you’ll find the SQL Statement I used to retrieve the required data, and screenshot that shows a successful query.

SELECT Gender, IsActive, AdvisorID

FROM Cameron_CTU.dbo.Students

WHERE Gender = 'm'

AND IsActive = 'yes'

AND AdvisorID IN (1,3)

The second task asks for a list of students without a biography. Here’s the SQL Statement I used, and screenshot that shows a successful query.

Task 3 is a very simple statement asking what classes are in the English Department. This SQL statement involves a wild card just after the L in the word “English”. The SQL statement and its associated screenshot are below.

SELECT ClassCode, ClassName

FROM Cameron_CTU.dbo.Classes

WHERE ClassName LIKE 'ENGL%'

Task 4 calls for a list of all students and their advisors, and sorted by Advisors, and a few columns from the Students table

SELECT A.AdvisorID, A.FirstName, A.LastName,

S.AdvisorID, S.FirstName, S.LastName,BirthDate, Gender, GPA

FROM Cameron_CTU.dbo.Students S, Cameron_CTU.dbo.Advisors A

WHERE S.AdvisorID = A.AdvisorID

ORDER BY A.LastName, S.LastName ASC;

SELECT A.CustomerID, A.FirstName, A.LastName,

S.AdvisorID, S.FirstName, S.LastName,BirthDate, Gender, GPA

FROM Cameron_CTU.dbo.Students S, Cameron_CTU.dbo.Advisors A

WHERE S.AdvisorID = A.AdvisorID

ORDER BY A.LastName, S.LastName ASC;

The fifth task calls for the number of students born in the 80s. Below, you’ll find the SQL Statement I used to retrieve the required data, and screenshot that shows a successful query.

SELECT COUNT (StudentID) as Students_born_in_the_80s

FROM Cameron_CTU.dbo.Students

WHERE BirthDate between '1980-01-1' AND '1989-12-31';

Task 6

Task 6 wanted to know the average GPA score sorted by men and women. Here is the SQL code and screenshot.

Task 7

Task 7 is looking for all the advisors with only 1 student. Here’s the SQL code and screenshot.

SELECT A.firstname, A.LastName,

COUNT (A.AdvisorID) AS Total_Active_Students

FROM Cameron_CTU.dbo.Students S,

Cameron_CTU.dbo.Advisors A

WHERE A.AdvisorID = S.AdvisorID AND S.IsActive = 'yes'

GROUP BY A.FirstName, A.LastName

HAVING COUNT (S.StudentID) = '1';

Stage 4 requires an approach to track classes required for degrees, and which degree understudies are seeking after. We were given the errand of making a table for following degrees with Degree Name and Degree Description. Nonetheless, the most ideal approach to track the classes required for degrees is to make a table named "Degrees" with sections marked 'DegreeID', 'Degree Name' and 'Degree Description', and 'Degree Code'. Additionally, adding a segment to the "Classes" table, named DegreeID will help connect the "Degrees" and "Classes" tables. After the tables have been made, I'll have to test the engineering to guarantee it can render the outcomes I'm searching for in questions. (Fehily, 2008)

Be that as it may, I simply got my review for my Phase 1 IP with distinguished mistakes. See Phase 1 area for settled mistakes. The blunders that need rectified are:

- Considerations with respect to my information sorts and field lengths: FirstName and LastName too long

Fix imperatives for assignments 1 through 4 to guarantee just 0-100 can be information.

- Fix GPA Constraint to guarantee contribution of just 0.00 – 4.00, and allow just 3 add up to digits after decimal.

- Constrain "Sex" to either M or F, and IsActive to either Yes or No.

I’ll start by creating the table, ‘Degrees’ with the following DDL script, followed by a screenshot of the query being completed successfully, and the table structure that displays the table columns to include the PRIMARY KEY, ‘DergeeID’.

USE [Cameron_CTU]

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Degrees]

(

[DegreeID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[DegreeName] [varchar](50) NOT NULL,

[DegreeDescription] [varchar](1000) NOT NULL,

[DegreeCode] [varchar](50) NOT NULL,

)

The next step is to delete and re-create the ‘Classes’ table, and ensure that the ‘DegreeID’ column is included in the creation, and made a FOREIGN KEY. Also, ensure you re-insert the original 3 classes, and add 3 new classes into the ‘Classes’ table required by different degrees. And since you’ve re-created the ‘Classes’ table with ‘DegreeID’ as a FOREIGN KEY, make sure you add it to the INSERT INTO DDL script or you’ll receive an error regarding the ‘DegreeID’ column. Below are the following DDL scripts, followed by a screenshot of the queries being completed successfully, and the table structure that displays the table columns with ‘DegreeID’ as a FOREIGN KEY, and the query results of the SELECT * FROM Classes query.

USE [Cameron_CTU]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Classes]

(

[ClassID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

[DegreeID] [int] NOT NULL,

[ClassCode] [varchar](50) NOT NULL,

[ClassName] [varchar](100) NOT NULL,

[Description] [varchar](1000) NOT NULL,

)

ALTER TABLE [dbo].[Classes]

ADD FOREIGN KEY([DegreeID])

REFERENCES [dbo].[Degrees]([DegreeID])

GO

INSERT INTO [Cameron_CTU].[DBO].[Classes]

( [DegreeID], [ClassCode], [ClassName], [Description] )

VALUES

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

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

( '3', 'ENGL115', 'English Composition', 'In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive

essay techniques.' ),

( '3', 'BHVS205', 'Managerial Psychology', 'This course introduces Psyhology concepts that will serve as the foundation to other concepts, such as Motivation and Emotion and Interpersonal

Communications and Dynamics.' ),

( '4', 'CSS150', 'Introduction to Computer Security', 'This course covers Computer Security Principles, such as Policy letters, Remote Logins, and Network Security.' ),

( '5', 'CS126', 'UNIX Fundamentals', 'This course teaches you thed origins of the UNIX Operating System, and teaches you how to build directories.' )

As the paragraph title implies, the next step is to add the ‘DegreeID’ column to the ‘Students’ table, and make it a FOREIGN KEY. Below is the following DDL script used to add the column, and make it a FOREIGN KEY, and the screenshot of the successful command.

USE [Cameron_CTU]

GO

ALTER TABLE Students

ADD DegreeID [int] NOT NULL

GO

ALTER TABLE Students

ADD FOREIGN KEY (DegreeID)

REFERENCES Degrees(DegreeID)

Step 4: DML script to insert 3 test records in [Degrees]

The next step is to insert degree data into the ‘Degrees’ table. Below is the DML script used, and a screenshot of the successful command along with an inset of the results for a SELECT * FROM Degrees statement.

INSERT INTO [Cameron_CTU].[DBO].[Degrees]

( [DegreeCode], [DegreeName], [DegreeDescription] )

VALUES

( 'BSIP', 'Bachelor of Science in Psychology', 'The Bachelor’s degree in General Psychology is designed to prepare students to successfully navigate in the 21st century workplace, in a variety of careers that focus on the business of people, including but not limited to work in management, administration, research, and sales. It is positioned to provide an overview of the major psychological concepts, perspectives, and skills that explain human behavior. ' ),

( 'BSIS', 'Bachelor of Science in Information Security', 'The Bachelors of Science in Information Assurance and Security degree allows undergraduate learners to acquire and apply various processes, tools, technologies, and methods of securing an enterprise; including security policies, social engineering, access control, authentication, perimeter security, disaster recovery and business continuity, risk management, incident response, viruses, malware, spam, encryption, and other infrastructure security techniques that include governance and strategic alignment of IT and business. ' ),

( 'BSIT', 'Bachelor of Science in Information Technology', 'The Bachelor of Science in Information Technology (BSIT) curriculum includes both a common core, as well as specialization-specific courses. The BSIT core provides a strong foundation in the key information technology areas of programming, systems administration, security, architecture, databases, and ethics.' )

The next step is to add the ‘DegreeID’ to 4 records applicable to each of the 4 students. Below is the DML script used to execute the command and a screenshot showing it was successful.

INSERT INTO [Cameron_CTU].[dbo].[Students]

( [FirstName],

[LastName],

[BirthDate],

[Gender],

[StartDate],

[GPA],

[IsActive],

[Bio],

[AdvisorID],

[DegreeID] )

VALUES

( 'Craig', 'Franklin', '1970-03-15', 'm', '2010-05-30', 3.10, 'Yes', NULL, 3, 3 ),

( 'Harriet', 'Smith', '1982-04-15', 'f', '2010-05-30', 3.22, 'Yes', NULL, 1, 4 ),

( 'George', 'David', '1984-11-05', 'm', '2010-10-01', 0.00, 'Yes', NULL, 3, 5 ),

( 'Ben', 'Jefferson', '1976-09-25', 'm', '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, 3 )

The last step is to execute a SELECT statement with an implicit JOIN clause to show what degree each student is pursuing.

USE Cameron_CTU

SELECT FirstName, LastName, Students.DegreeID, Degrees.DegreeName

FROM Cameron_CTU.dbo.Students, Cameron_CTU.dbo.Degrees

WHERE Students.DegreeID = Degrees.DegreeID;

Fehily, C. (2008) Visual QuickStart Guide SQL, Third Edition, [CourseSmart e-Book reader version] Retrieved from http://wow.coursesmart.com/9781256745129/firstsection