Converting English Language to Sql queries(HELP!!)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting English Language to Sql queries(HELP!!)

Author

Topic

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-21 : 11:16:32

7) What is the full name of all instructors that have been qualified by Jim Carrey. Also list the courses they were qualified to teach. Show the list in order by instructor last name and then course name.
8) Write a query that could be used to double-check the TotalPurchase on the Purchase table. Your query should show a list of all purchases with three columns starting with the Transaction numbers of each purchase transaction. The second column should be the value that is currently stored as the TotalPurchase. The third column should recalculate the total purchase amount without using the ActualPrice on the Includes table or the TotalPurchase value on the Purchase table. Instead, use the standard price and their current discount rate.
9) List the course names and start dates and start times of all courses that are offered on Wednesday afternoons that start AFTER 3:00PM.
10) Provide an alphabetical list of instructor names that were either qualified as instructors in 2014 or instructed a class that started in 2014 (or both). Do not list any instructor more than once.
11) List the full names of Platinum members that have never made a purchase.
12) List the name of every member followed by their membership type and the total number of courses they have taken. Show the list in alphabetical order by name within descending order by number of courses taken. Include all members in the list even if they have not taken any courses.
13) Write a query to check to see if a course is scheduled into a room that does not have the proper equipment for that course. List the class reference number, course number and course name and room number if there is a problem.
14) List the names of all Store Clerks followed by their total number of sales transactions and their total sales amounts. Only list them if they have made more than 1 sale. List them in order of highest total sales amounts first.
15) Parking is becoming a problem around the fitness center so we are going to issue parking permits to all our members and employees. To do this, we need to add a new column named ParkingPermit to the person table to store the permit number that will be assigned to each person. This column should be an integer that does not allow nulls and defaults to zero.
16) Jerry Springer gets kicked out of the Fitness Center and must be disenrolled from all classes and deleted from the database. This may require more than one SQL command.

Slim Shady

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 12:39:20

Sounds like homework to me.

You're on your own there.

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-21 : 13:31:41

I dont need it done completely for me Mr. Gbritton Sir
All am asking is using these examoples can some you use and show me a similar example ,so I can get grisp of these assignment .
I would do them my self just need directions on how to go about them.
Thanks.

Slim Shady

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-21 : 13:39:23

Ok to get started

1. You will write a SELECT query against one or two tables. Since you didn't mention any tables, I'll assume s table called Instructors with columns InstructorId FirstName, LastName and QualifiedyBy and another called Courses with columns CourseId, InstructorId and CourseName. Your first query would look something like this:


SELECT i.FirstName + ',' + i.LastName
FROM Instructors i
JOIN Courses c
  ON i.InstructorId = c.InstructorId
WHERE i.QualifiedyBy  = 'Jim Carrey';
ORDER BY i.LastName, i.FirstName, c.CourseName

rigidBoy1992
Starting Member

11 Posts

Posted - 2014-11-22 : 10:33:18

Appreciative sir Gbritton
The hard thing am having is how to link the Person table that is
super type and has all the names that subtypes don't have like this is the structure
Person(lname,fname,Gender,Dob,city,street, address,....)
Employee can be (store_clerk or Instructor) They have pk that is Referring back to the person Tbale so their pks are fks from person table
Instructor(ID,DateQualified)...

Slim Shady

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-22 : 12:10:07

To link Person to Employee you need to join on the FK/PK relationship

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources