CODEWITHSUNDEEP
sql-servert-sql
user9433862
user9433862

Reputation: 19

SQL assignment: Using Variables and using IF statements

Okay, So here is the first question on the assignment. I just don't know where to start with the problem. If anyone could just help me get started I'd be able to figure it out probably. Thanks

  1. Set two variable values as follows:

@minEnrollment = 10 @maxEnrollment = 20

Determine the number of courses with enrollments between the values assigned to @minEnrollment and @maxEnrollment. If there are courses with enrollments between these two values, display a message in the form

There is/are __class(es) with enrollments between __ and __..

If there are no classes within the defined range, display a message in the form “ There are no classes with an enrollment between __ and __ students.” .....

And here is the database to use:

CREATE TABLE Faculty
(Faculty_ID INT  PRIMARY KEY IDENTITY,
LastName VARCHAR (20) NOT NULL,
FirstName VARCHAR (20) NOT NULL,
Department VARCHAR (10) SPARSE NULL,
Campus VARCHAR (10) SPARSE NULL);

INSERT INTO Faculty VALUES ('Brown', 'Joe', 'Business', 'Kent'); 
INSERT INTO Faculty VALUES ('Smith', 'John', 'Economics', 'Kent'); 
INSERT INTO Faculty VALUES ('Jones', 'Sally', 'English', 'South'); 
INSERT INTO Faculty VALUES ('Black', 'Bill', 'Economics', 'Kent'); 
INSERT INTO Faculty VALUES ('Green', 'Gene', 'Business', 'South'); 


CREATE TABLE Course
(Course_ID INT PRIMARY KEY IDENTITY,
Ref_Number CHAR (5) CHECK (Ref_Number LIKE '[0-9][0-9][0-9][0-9][0-9]'),
Faculty_ID INT NOT NULL REFERENCES Faculty (Faculty_ID),
Term CHAR (1) CHECK (Term LIKE '[A-C]'),
Enrollment INT NULL DEFAULT 0 CHECK (Enrollment < 40))

INSERT INTO Course VALUES ('12345', 3, 'A', 24); 
INSERT INTO Course VALUES ('54321', 3, 'B', 18); 
INSERT INTO Course VALUES ('13524', 1, 'B', 7); 
INSERT INTO Course VALUES ('24653', 1, 'C', 29); 
INSERT INTO Course VALUES ('98765', 5, 'A', 35); 
INSERT INTO Course VALUES ('14862', 2, 'B', 14); 
INSERT INTO Course VALUES ('96032', 1, 'C', 8); 
INSERT INTO Course VALUES ('81256', 5, 'A', 5); 
INSERT INTO Course VALUES ('64321', 2, 'C', 23); 
INSERT INTO Course VALUES ('90908', 3, 'A', 38);

Upvotes: 0

Views: 1224

Answers (3)

kooshy
kooshy

Reputation: 134

One step at a time. Let me give you some help:

Set two variable values as follows: @minEnrollment = 10 @maxEnrollment = 20

Translated to SQL, this would look like:

Declare @minEnrollment integer = 10
Declare @maxEnrollment integer =15
Declare @CourseCount integer = 0

Determine the number of courses with enrollments between the values assigned to @minEnrollment and @maxEnrollment.

Now you have to query your tables to determine the count:

SET @CourseCount = (SELECT Count(Course_ID) from Courses where Enrollment > @minEnrollment

This doesn't answer your questions exactly (ON PURPOSE). Hopefully you can spot the mistakes and fix them yourself. The other answers gave you some helpful hints as well.

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

Your request is how to get started, so I'm going to focus on that instead of any specific code.

Start by getting the results that are being asked for, then move on to formatting them as requested.

First, work with the Course table and your existing variables, @minEnrollment = 10 and @maxEnrollment = 20, to get the list that meets the enrollment requirements. Hint: WHERE and BETWEEN. (The Faculty table you have listed doesn't factor into this at all.) After you're sure you have the right results in that list, use the COUNT function to get the number you need for your answer, and assign that value to a new variable.

Now, to the output. IF your COUNT variable is >0, CONCATenate a string together using your variables to fill in the values in the sentence you're supposed to write. ELSE, use the variables to fill in the other sentence.

Upvotes: 2

Kevin
Kevin

Reputation: 2243

Part of the problem is, you've actually got 3 or so questions in your post. So instead of trying to post a full answer, I'm instead going to try to get you started with each of the subquestions.

Subquestion #1 - How to assign variables.

You'll need to do some googling on 'how to declare a variable in SQL' and 'how to set a variable in SQL'. This one won't be too hard.

Subquestion #2 - How to use variables in a query

Again, you'll need to google how to do this - something like 'How to use a variable in a SQL query'. You'll find this one is pretty simple as well.

Subquestion #3 - How to use IF in SQL Server.

Not to beat a dead horse, but you'll need to google this. However, one thing I would like to note: I'd test this one first. Ultimately, you're going to want something that looks like this:

IF 1 = 1   -- note, this is NOT the correct syntax (on purpose.)
    STUFF
ELSE
    OTHERSTUFF

And then switch it to:

IF 1 = 2   -- note, this is NOT the correct syntax (on purpose.)
    STUFF
ELSE
    OTHERSTUFF

... to verify the 'STUFF' happens when the case is true, and that it otherwise does the 'OTHERSTUFF'. Only after you've gotten it down, should you try to integrate it in with your query (otherwise, you'll get frustrated not knowing what's going on, and it'll be tougher to test.)

Upvotes: 2

Related Questions