SQL queries - SQL Server Forums

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

SQL queries

Author

Topic

deathrow
Starting Member

6 Posts

Posted - 2010-04-25 : 12:39:20

Hello, i have done 8 queries out of 10 and need 2 more but i am struggling, could someone help me out please.

My entities are Employee, Location, Booking, fee, registration, course, course type, trainer, trainee

Im not sure what more information i need to provide, but will do on request.

I need this a.s.a.p

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-25 : 13:04:33

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

deathrow
Starting Member

6 Posts

Posted - 2010-04-25 : 14:10:02

Hello,

I need 10 queries, ive got 8 which i will post up later.

Heres my DDL

create table course(
course_id int not null,
course_title varchar(30) not null,
course_cid varchar(1) not null,
course_level varchar(2) not null,
constraint course_course_id_pk primary key (course_id));

create table coursetype(
coursetype_id int not null,
coursetype_title varchar(70) not null,
course_level varchar(2) not null,
constraint coursetype_coursetype_id_pk primary key (coursetype_id));

create table fees(
fees_id int not null,
fees_fid varchar(1) not null,
fees_fee varchar(7) not null,
course_cid varchar(1) not null,
constraint fees_fees_id_pk primary key (fees_id));

create table trainer(
trainer_id int not null,
trainer_name varchar(30) not null,
fees_fid varchar(1) not null,
constraint trainer_trainer_id_pk primary key (trainer_id));

create table trainee(
trainee_id int not null,
trainee_name varchar(30) not null,
trainee_registration date not null,
trainer_id int not null,
constraint trainee_trainee_id_pk primary key (trainee_id)
constraint trainee_trainer_id_fk foreign key (trainer_id) references trainer(trainer_id));

create table location(
location_id int not null,
location_lid varchar(2) not null,
location_name varchar(30) not null,
constraint location_location_id_pk primary key (location_id));

create table employees(
employee_id int not null,
employee_name varchar(70) not null,
employee_role varchar(30) not null,
employee_eid varchar(2) not null,
course_cid varchar(1) not null,
location_lid varchar(2) not null,
constraint employees_employee_id_pk primary key (employee_id));

create table booking(
booking_id int not null,
booking_date date not null,
booking_time varchar(5) not null,
employee_id int not null,
location_lid varchar(2) not null,
constraint booking_booking_id_pk primary key (booking_id)
constraint booking_employee_id_fk foreign key (employee_id) references employees(employee_id));

create table registration(
registration_id int not null,
registration_date date not null,
registration_time varchar(5) not null,
location_lid varchar(2) not null,
constraint registration_registration_id_pk primary key (registration_id));

DML samples

(1) Booking table -

insert into booking values(
'1','03-MAY-10','09.00','1','DL');

insert into booking values(
'2','03-MAY-10','09.30','1','DL');

insert into booking values(
'3','03-MAY-10','10.00','1','DL');

Attempted DML

select course_id, course_name, course_cid, coursetype_name
from course, coursetype
where course.course_level = coursetype.course_level
order by course_name, coursetype_name;

My queries so far:

(1) Sorting: Select all Booking in the Booking table and group it by the Booking time.

select * from booking
group by booking_time;

(2) Update: Update Location table, by setting ‘HW’ to ‘Holloway, where the location is stated ‘DL’.

update location
set location_lid='HW', location_name='Holloway'
where location_lid='DL';

(3) Joining Tables: List Trainee Name, Id and Trainer Name and join Trainee Table and Trainer Table.

select trainee_id, trainee_name, trainer_name
from trainee, trainer
where trainer.trainer_id = trainee.trainer_id;

(4) Grouping: List courses and group them by Course Level or Course Title.

select course_id, course_title, course_level
from course
where course_level='L3'
group by course_title, course_level;

(5) Where Clause: List Booking Date and Booking Time where the Booking Date is 03 May 10.

select booking_id, booking_date, booking_time
from booking
where booking_date='03-MAY-10';

(6) Logical Operator (Between): List all booking times between 10.00 and 11.00.

select booking_time
from booking
where time
between 10.00 and 11.00;

(7) Operator (Like): List all names that have the letter C in.

select trainee_name
from trainee
where trainee_name like ‘C%’;

(8) Comparison Operator (Not Equal): List all employees whose name is not Steve Smith.

select employee_name
from employee
where employee_name != ‘Steve Smith’

I need 2 more for set functions and sub queries

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources