reate table queries CREATE TABLE Students( StudentID INT NOT NULL PRIMARY KEY, LastName VARCHAR(25) NOt NULL, FirstName VARCHAR(25) NOT NULL, EnrollmentDate DateTime2(0) NOt NULL, GraduationDate Date NULL ) CREATE TABLE Courses( CourseID INT NOT NULL PRIMARY KEY, CourseNumber INT NULL, CourseDescription VARCHAR(50) NOT NULL, CourseUnits INT NOT NULL, DepartMentID INT NOT NULL, InstructorID INT NOT NULL ) CREATE TABLE StudentCourses( StudentID INT NOT NULL, CourseID INT NOT NULL, PRIMARY KEY(StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) ) CREATE TABLE Tuition( PartTimeCost SMALLMONEY NOT NULL, FullTimeCost SMALLMONEY NOT NULL, PerUnitCost SMALLMONEY NOT NULL ) arrow_forward Step 3 CREATE FUNCTION fnStudentUnits ( @studentID INT ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @totalUnits INT SET @totalUnits = 0 SELECT @totalUnits = SUM(CourseUnits) FROM StudentCourses sc INNER JOIN Students s ON s.StudentID = sc.StudentID INNER JOIN Courses c ON c.CourseID = sc.CourseID -- Return the result of the function RETURN @totalUnits END GO 1. Using the Code above please create three tests: 1) test for a student who has courses
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
Create table queries
CREATE TABLE Students(
StudentID INT NOT NULL PRIMARY KEY,
LastName VARCHAR(25) NOt NULL,
FirstName VARCHAR(25) NOT NULL,
EnrollmentDate DateTime2(0) NOt NULL,
GraduationDate Date NULL
)
CREATE TABLE Courses(
CourseID INT NOT NULL PRIMARY KEY,
CourseNumber INT NULL,
CourseDescription VARCHAR(50) NOT NULL,
CourseUnits INT NOT NULL,
DepartMentID INT NOT NULL,
InstructorID INT NOT NULL
)
CREATE TABLE StudentCourses(
StudentID INT NOT NULL,
CourseID INT NOT NULL,
PRIMARY KEY(StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
)
CREATE TABLE Tuition(
PartTimeCost SMALLMONEY NOT NULL,
FullTimeCost SMALLMONEY NOT NULL,
PerUnitCost SMALLMONEY NOT NULL
)
CREATE FUNCTION fnStudentUnits
(
@studentID INT
)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @totalUnits INT
SET @totalUnits = 0
SELECT @totalUnits = SUM(CourseUnits)
FROM StudentCourses sc
INNER JOIN Students s ON s.StudentID = sc.StudentID
INNER JOIN Courses c ON c.CourseID = sc.CourseID
-- Return the result of the function
RETURN @totalUnits
END
GO
1. Using the Code above please create three tests:
1) test for a student who has courses
2) test for student who does not have courses
3) a non-existing student ID. For each test, display the value of the student ID that was passed to the function and the result returned by the function. Also, run a supportive SELECT query or queries that prove the test results are correct.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
