0

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);
Improve this question
10
  • 1
    Read this 2 or 3 times and I can't see the question, you have a lot of the information which will be needed to assist community members with answering your question however it requires edits that others cannot provide due to the current state of the question at the time of this comment. Commented Dec 7, 2018 at 19:41
  • 2
    @Jay Sorry, I'm new here, I'll understand more about how to make proper questions in the future. Someone edited it and it looks much better now. I think the object of this problem is to Print one of the two statements based on the variables I create. Commented Dec 7, 2018 at 19:46
  • 2
    It would be helpful for you to post what you have tried so that we can see what your level of knowledge is. You may want to try reading up on COUNT. Commented Dec 7, 2018 at 19:48
  • @a_horse_with_no_name I am using Microsoft SQL Server Commented Dec 7, 2018 at 19:48
  • 1
    this is not chegg.com / homework helper buddy good ol' chap. You could at least try to make it look like something you are doing at work. Instead you are clearly telling us it's a homework assignment. You are asking 10 different things. Ask a single question and we will happily answer Commented Dec 7, 2018 at 19:51

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.)

Improve this answer

2 Comments

By the way, I'd say that this skill is probably one of the most important elements to being a programmer: being able to break down a task into subtasks. If a subtask seems too difficult, break it down even further.
And I'd say that's absolutely true. +1.
0

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.

Improve this answer

6 Comments

You realize you just did their assignment for them, right?
I helped with parts of it. It's difficult for a beginner to be able to digest the clues and hints you are giving him. We all were there once.
... I take it you realize, from your edits, that you did indeed write his assignment for him? Just make sure to keep it mind going forward - like a commenter said, we're not supposed to be a homework writing service. If someone asks a single specific question ("How do I declare variables in SQL?") then we should give an explicit answer. But Eric and I were answering this very strategically - pointing him to specific phrases to google to learn how it works.
I edited the answer so it doesn't exactly solve it. If you guys want me to take it down I'd be more than happy to.
For my money, I'd leave this up. Personally, I have no issue helping with homework so long as we're just helping, which seems to be what the OP here was really looking for. All three answers posted (so far, anyway) demonstrate different approaches to that.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.