SQL Help

[Rekindled]

Request: SQL software to practice exercise questions with.

Hey I have a database exam on Tuesday and my tutor said he would upload some sample questions today for us to practice at home. He also included some software that we could use to practice with. with the footnote.

"Use windows2000/XP; may not work on vista and 7" which it doesn't.

Does anyone know of any software that I could use to practice with?

Questions: In-case anyone is wondering


This week’s exercise is based on the following (simplified) database schema:

Student (sID, lastname, firstname, gender, phone)

Course (cID, title, description)

Faculty (fID, name, title, email)

CourseOffer (cID, year, semester, fID, venue)

FK: cID references cID in course table
fID references fID in faculty table

Enrolment(sID, cID, year, semester, grade)
FKs: sID references sID in student table
(cID, year, semester) references corresponding attributes in CourseOffering table


Create the above tables in Oracle, enforcing all keys and foreign key constraints, as well as the following constraints:

gender must be either ‘F’ or ‘M’
semester must be either ‘1’ or ‘2’
grade must be one of ‘F’,’P’,C’,’D’ ‘HD’
title of faculty must be ‘Prof’, ‘Apro’, ‘Dr’, ‘Mr’ or ‘Ms’
Only phone,venue and grade can take null values.
email is an alternate key for faculty, i.e., no two faculty members share an email address.
If a course offering is dropped, all enrolment data for that offering should be dropped;


2. Insert some sample data into the tables.

Note: it is better to write a script file to create tables, insert data, drop tables etc.
Assign data type to each attribute as you think appropriate.

3. Find all students (by student ID and name) who do not have a phone number recorded in the table, in ascending order of last name.
4. Find all courses that are offered in semester 1, 2010 but not in semester 1, 2011.
5. Find all students enrolled in 3512ICT in semester 1, 2011.
6. Find the number of female students enrolled in 2002ICT, in semester 1, 2011.
7. Find the number of students who received F, P, C, D, and HD respectively in 1420ICT in semester 2, 2010.
8. Find all students by (ID and name) who enrolled in less than 2 courses in semester 1, 2011.
9. Find the courses which have an enrolment of less than five students in semester 1, 2011.
10. List the names of those students who received HD in 1420ICT in semester 2, 2010.
11. List all faculties by name, and if the faculty teaches a course in semester 1, 2011, also list the course id.
12. Find all students who received D or HD in every course he/she enrolled in in semester 2, 2010.
13. Find all students who enrolled in some courses and received D or HD in every course he/she enrolled in in semester 2, 2010.
14. Modify the structure of the table student to add an attribute email.
15. Change the grade to HD for students enrolled in 2002ICT, semester 1, 2011 and whose grade is D
16. find all courses whose description contains the word ‘database’;

[Feriuma]

First of all, I barely know anything about SQL, I've only used it to a very small extent when I played around with some android->servlet->sql magic.
My dad set up the database for me and the program I've used is called Enterprise Manager. (http://en.wikipedia.org/wiki/Enterprise_Manager)

I don't have anything to compare with, but it suits my basic needs. The SQL Query Analyzer inside it opens up many possibilities aswell.

[Ulminia]

WampServer this is your friend basic and full php apachie and mysql server roled in to 1 install and its easy to use and includes phpmyadmin a web interface for making sql tables and inserting data

[Aeiri]

I personally vouch for WampServer. I have always used it for my php/mysql programming from home and haven't really met any real problems with it.

[Cyanotical]

MySQL and Microsoft SQL express 2005 are free iirc, you will want to install SQL in a VM, SQL tends to screw up an OS install when it is removed