Practice sql to learn SQL syntax

By Sergey

You can use this web page to practice writing SQL select queries. Of cause, SQL queries are not limited just to select statement. There is update, delete and insert SQL statement, but SELECT SQL statement is the most important and most difficult to learn. Why SELECT SQL statement is most important. Because to write correct update or delete SQL statement, you must be able to select correct database record that you want to update or delete.

Let us imagine that we have a computer school where we teach different computer programming classes: Java, Visual basic, C++ … For our computer school management, we created a database with the following tables.

One table is for teachers, one for students and one for computer courses. To link tables to each other, we created teachers-schedule table and students-courses table. Fields for these tables are shown in the table below:

Computer School Database Structure
students table: studentid lastname firstname email phone age gender startdate	teachers table: teacherid lastname firstname email phone hiredate rate	courses table: courseid coursename hours cost
schedule table: scheduleid courseid teacherid starttime endtime startdate	student_course table: studentcourseid studentid courseid paid

Computer School Database Structure

students table:

studentid
lastname
firstname
email
phone
age
gender
startdate

teachers table:

teacherid
lastname
firstname
email
phone
hiredate
rate

courses table:

courseid
coursename
hours
cost

schedule table:

scheduleid
courseid
teacherid
starttime
endtime
startdate

student_course table:

studentcourseid
studentid
courseid
paid

For example, if we want to know which student is older than 21 and we write a query:

SELECT * FROM students WHERE age > 21

Or we want to get information a student whose first name is Michael:

SELECT * FROM students WHERE firstname='Michael'

You see how it is ease... Let us try somethng more complecated...

Students who take Java class:

SELECT students.firstname, students.lastname from students JOIN student_course ON students.studentid=student_course.studentid JOIN courses ON student_course.courseid=courses.courseid WHERE courses.coursename='Java'

The teacher name who teaches Java class:

SELECT t.firstname, t.lastname FROM teachers t JOIN schedule s ON t.teacherid=s.teacherid JOIN courses c ON s.courseid=c.courseid WHERE c.coursename='Java'

Copy the query, paste it in the text area and click Submit button. The query result will be dusplayed!"

I gave this page user only permission for executing select queries.

Try to write queries on your own. To find out what data is in any table execute query "SELECT * FROM [table name]"

Write queries:
1. Find which couses Michael Murphy is teaching.
2. Find which hours teaher Michael Murphy has classes.
3. Find which hours student Michael has classes.
3. Find what is student age in Java class.
4. Find at what hour PHP class starts.

I will place answers later

Your Query Result
studentid	lastname	firstname	email	phone	age	gender	startdate
1	Bored	Jack	jackb@email.com	727-193-0987	21	male	2010-01-11
2	Funny	Molly	mollyf@email.com	727-234-0987	20	female	2010-01-11
3	Merry	Michael	michaelm@email.com	727-987-0987	22	male	2010-01-11
4	Crying	Jesica	Jesicac@email.com	727-987-1234	22	female	2010-01-11
5	Beauty	Maria	mariab@email.com	727-987-3333	21	female	2010-01-11
6	Geeky	George	georgeg@email.com	727-234-3334	18	male	2010-01-11
7	Nerdy	James	jamesn@email.com	727-234-5555	23	male	2010-01-11
8	Laughing	John	johnl@email.com	727-234-5559	21	male	2010-01-12
9	Humorous	Ryan	ryanh@email.com	727-234-5559	20	male	2010-01-12
10	Depressed	Carly	carlyd@email.com	727-234-5559	20	female	2010-01-12

Did you find information useful? Send to your friend a link to this page

Please rate the tutorial

Comments

Computer Programming Web programming Tips

Practice sql to learn SQL syntax