Path 2 (Click Here For SQL Analysis)

CS290F Fall 2006 - UCSB Computer Science - Thorsten von Eicken

Jump to: navigation, search

Contents

[edit]

Homepage

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER 
BY average DESC
Music Load SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY 
average DESC LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4)
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5)
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6)
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9)
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2)
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7)
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8)
SELECT * FROM ratings WHERE (ratings.music_id = 8)
[edit]

Browse top rated

[edit]

User goes to page 2 of the top rated songs:

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER
BY average DESC
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC
LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 11)
SELECT * FROM ratings WHERE (ratings.music_id = 11)
[edit]

User goes back to page 1 of the top rated songs:

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER
BY average DESC
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC 
LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
[edit]

Listen to top rated songs

[edit]

Add top 3 songs to playlist (Cart)

[edit]

User adds top rated song to cart:

SELECT * FROM musics WHERE (musics.id = '1') LIMIT 1
SHOW FIELDS FROM musics
[edit]

Pages gets refreshed after adding to cart:

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER 
BY average DESC
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC 
LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7) 
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
[edit]

User adds another top rated song to cart:

SELECT * FROM musics WHERE (musics.id = '3') LIMIT 1
SHOW FIELDS FROM musics
[edit]

Pages gets refreshed after adding to cart:

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER 
BY average DESC
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC 
LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
[edit]

User adds a third top rated song to cart:

SELECT * FROM musics WHERE (musics.id = '4') LIMIT 1
SHOW FIELDS FROM musics
[edit]

Pages gets refreshed after adding to cart:

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER 
BY average DESC
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC 
LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
[edit]

Login

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`login` = 'dominic' ) LIMIT 1

EXPLAIN SELECT * FROM users WHERE (users.`login` = 'dominic' ) LIMIT 1;
Before:
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

After:
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | login_index   | login_index | 258     | const |    1 | Using where | 
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-------------+
[edit]

Checkout

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 10 ) LIMIT 1
SHOW FIELDS FROM orders
SHOW FIELDS FROM musics
[edit]

Place Order

[edit]

User places order:

SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 10 ) LIMIT 1
SHOW FIELDS FROM orders
SHOW FIELDS FROM line_items
SHOW FIELDS FROM musics
BEGIN
COMMIT
BEGIN
COMMIT
BEGIN
COMMIT
BEGIN
INSERT INTO orders (`city`, `name`, `name_cd`, `zipcode`, `cc_cvv`, `pay_type`, `street_address`, `cc_num`, `cc_exp`, `user_id`, `email`, `state`)  
VALUES('Goleta', 'Dominic Metzger', 'Dominic\'s favorites', '93117', '345', 'cc', '210 SB', '4534534543', '34534', 10, 'dominic@ole.com', 'CA')
INSERT INTO line_items (`order_id`, `total_price`, `quantity`, `music_id`) VALUES(8, 0.7, 1, 1)
INSERT INTO line_items (`order_id`, `total_price`, `quantity`, `music_id`) VALUES(8, 0.6, 1, 3)
INSERT INTO line_items (`order_id`, `total_price`, `quantity`, `music_id`) VALUES(8, 0.6, 1, 4)
COMMIT


[edit]

User gets redirected back to the index page:

SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER 
BY average DESC
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC 
LIMIT 10
SHOW FIELDS FROM musics
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
SHOW FIELDS FROM users
SELECT * FROM users WHERE (users.`id` = 10 ) LIMIT 1
[edit]

Logout

[edit]

User logs out:

SHOW FIELDS FROM users�
SELECT * FROM users WHERE (users.`id` = 10 ) LIMIT 1�
BEGIN�
UPDATE users SET `created_at` = '2006-11-03 16:11:56', `login` = 'dominic', `crypted_password` = '1466aa2d40bfcf4a4bda5240dd5c5cc48bd3e740',  
`remember_token_expires_at` = NULL, `salt` = '3a65e03b11020a54dedab43d3cfc1cce1c00e525', `remember_token` = NULL, `email` = 'dominic', `updated_at` = 
'2006-11-03 17:02:02' WHERE id = 10�
COMMIT�
[edit]

After logging out, user gets redirected back to the index page:


SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC�
SELECT musics.*, avg(ratings.score) AS average FROM ratings, musics WHERE ratings.music_id = musics.id GROUP BY ratings.music_id ORDER BY average DESC 
LIMIT 10�
SHOW FIELDS FROM musics�
SHOW FIELDS FROM ratings
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 1) 
SELECT * FROM ratings WHERE (ratings.music_id = 1)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 3) 
SELECT * FROM ratings WHERE (ratings.music_id = 3)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 4) 
SELECT * FROM ratings WHERE (ratings.music_id = 4)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 5) 
SELECT * FROM ratings WHERE (ratings.music_id = 5)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 6) 
SELECT * FROM ratings WHERE (ratings.music_id = 6)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 9) 
SELECT * FROM ratings WHERE (ratings.music_id = 9)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 2) 
SELECT * FROM ratings WHERE (ratings.music_id = 2)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 10)
SELECT * FROM ratings WHERE (ratings.music_id = 10) �[0m
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 7) 
SELECT * FROM ratings WHERE (ratings.music_id = 7)
SELECT count(*) AS count_all FROM ratings WHERE (ratings.music_id = 8) 
SELECT * FROM ratings WHERE (ratings.music_id = 8)
Retrieved from "http://www.voneicken.com/courses/ucsb-cs290f-fa06/index.php/Path_2_%28Click_Here_For_SQL_Analysis%29"
Personal tools