SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL class assignment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wordizbon
Starting Member

USA
4 Posts

Posted - 11/16/2012 :  17:56:25  Show Profile  Reply with Quote
I have an SQL assignment to do but this part of the topic I got lost in. Anyone willing to assist in me completing this asap?

GetSome
Starting Member

4 Posts

Posted - 11/16/2012 :  18:24:25  Show Profile  Reply with Quote
I can but first I need to know which washing powder Lucy uses?

Edited by - GetSome on 11/16/2012 18:26:03
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/16/2012 :  22:10:00  Show Profile  Reply with Quote
You need to explain us what the problem is and what your attempts were. Without you trying out first we wont help solving assignments!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

wordizbon
Starting Member

USA
4 Posts

Posted - 11/16/2012 :  23:59:33  Show Profile  Reply with Quote
Not a problem. I can send it to you.
quote:
Originally posted by visakh16

You need to explain us what the problem is and what your attempts were. Without you trying out first we wont help solving assignments!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

wordizbon
Starting Member

USA
4 Posts

Posted - 11/17/2012 :  00:01:52  Show Profile  Reply with Quote
Here is the quiz:


CTS 2437 Assignment 9 Fall, 2012
For each of the following exercises, copy/paste both your SQL command and a screen shot of the results into a Word document. Please use one page per item. You should obtain the following results. Code used to complete each problem should be contained in the same batch.
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 thes 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.”
2. Select the courses for which Sally Jones has the most students and the least students. Assign these numbers to variables. Display results in the form:
The largest class taught by Sally Jones has an enrollment of _____. The smallest class taught by Sally Jones has an enrollment of ____. There is a difference of _____ between her smallest and largest class.
3. Create a table variable that contains a first name, last name, and reference number. Write statement(s) that will populate the table with the reference number for each course , along with the first and last name of the faculty member who teaches the course. Display contents of the table in the form :
[firstname] [lastname] [ref_number]
4. Repeat (3) using a local temporary table.
5. Createa global temporary table that contains each faculty member’s first name, last name, campus, and a new ID number consisting of 5 digits in the range 10000-99999. Each faculty number should be entered into the table only one time. Display contents of your table.
6. Write a dynamic SQL statement that, when executed, will display faculty first names, last names, course reference numbers, and course enrollment for each course. The table names should be assigned as variables
quote:
Originally posted by visakh16

You need to explain us what the problem is and what your attempts were. Without you trying out first we wont help solving assignments!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

wordizbon
Starting Member

USA
4 Posts

Posted - 11/17/2012 :  00:07:24  Show Profile  Reply with Quote
and I will attach the database that runs the file.



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

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 CHAR(2),
Ref_Number CHAR(5),
Faculty_ID INT REFERENCES Faculty(Faculty_ID),
Term Char(1),
Enrollment INTEGER);

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



CREATE TABLE Adjuncts
(Adjunct_ID Char(1),
LastName VARCHAR(20),
FirstName VARCHAR(20),
Department VARCHAR(10),
Campus VARCHAR(10));

INSERT INTO Adjuncts VALUES ('1', 'Rogers', 'Aaron', 'Business', 'Kent');
INSERT INTO Adjuncts VALUES ('2', 'Manning', 'Peyton', 'Economics', 'Kent');
INSERT INTO Adjuncts VALUES ('3', 'Brees', 'Drew', 'English', 'South');

quote:
Originally posted by wordizbon

Not a problem. I can send it to you.
quote:
Originally posted by visakh16

You need to explain us what the problem is and what your attempts were. Without you trying out first we wont help solving assignments!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000