Python for Everybody Database Handout
/lectures3/Pythonlearn-15-Database-Handout.txt
Download and Install: http://sqlitebrowser.org/
Single Table SQL
CREATE TABLE "Users" ("name" TEXT, "email" TEXT)
INSERT INTO Users (name, email) VALUES ('Chuck', 'csev@umich.edu')
INSERT INTO Users (name, email) VALUES ('Colleen', 'cvl@umich.edu')
INSERT INTO Users (name, email) VALUES ('Ted', 'ted@umich.edu')
INSERT INTO Users (name, email) VALUES ('Sally', 'a1@umich.edu')
INSERT INTO Users (name, email) VALUES c('Ted', 'ted@umich.edu')
INSERT INTO Users (name, email) VALUES ('Kristen', 'kf@umich.edu')
DELETE FROM Users WHERE email='ted@umich.edu'
UPDATE Users SET name="Charles" WHERE email='csev@umich.edu'
SELECT * FROM Users
SELECT * FROM Users WHERE email='csev@umich.edu'
SELECT * FROM Users ORDER BY email
SELECT * FROM Users ORDER BY name DESC
Multi-Table SQL:
CREATE TABLE "Artist" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"name" TEXT)
CREATE TABLE "Album" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
artist_id INTEGER,
"title" TEXT)
CREATE TABLE "Genre" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"name" TEXT)
CREATE TABLE "Track" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
album_id INTEGER, genre_id INTEGER, len INTEGER, rating INTEGER,
"title" TEXT, "count" INTEGER)
INSERT INTO Artist (name) VALUES ('Led Zepplin')
INSERT INTO Artist (name) VALUES ('AC/DC')
INSERT INTO Genre (name) VALUES ('Rock') ;
INSERT INTO Genre (name) VALUES ('Metal');
INSERT INTO Album (title, artist_id) VALUES ('Who Made Who', 2);
INSERT INTO Album (title, artist_id) VALUES ('IV', 1);
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Black Dog', 5, 297, 0, 2, 1) ;
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Stairway', 5, 482, 0, 2, 1) ;
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('About to Rock', 5, 313, 0, 1, 2) ;
INSERT INTO Track (title, rating, len, count, album_id, genre_id)
VALUES ('Who Made Who', 5, 207, 0, 1, 2) ;
SELECT Album.title, Artist.name FROM Album JOIN Artist
ON Album.artist_id = Artist.id
SELECT Album.title, Album.artist_id, Artist.id, Artist.name
FROM Album JOIN Artist ON Album.artist_id = Artist.id
SELECT Track.title, Track.genre_id, Genre.id, Genre.name
FROM Track JOIN Genre
SELECT Track.title, Genre.name FROM Track JOIN Genre
ON Track.genre_id = Genre.id
SELECT Track.title, Artist.name, Album.title, Genre.name
FROM Track JOIN Genre JOIN Album JOIN Artist
ON Track.genre_id = Genre.id AND Track.album_id = Album.id
AND Album.artist_id = Artist.id
Many-Many Relationship
CREATE TABLE User (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE,
email TEXT
) ;
CREATE TABLE Course (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE
) ;
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
role INTEGER,
PRIMARY KEY (user_id, course_id)
) ;
INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org');
INSERT INTO Course (title) VALUES ('Python');
INSERT INTO Course (title) VALUES ('SQL');
INSERT INTO Course (title) VALUES ('PHP');
INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0);
SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name
I laughed insolently. The young ladies sparkled, and so did Miss Harper, as she asked him who had been the proxy. He might have known by looking a trifle more narrowly; I saw plainly, thrillingly, who he was; but his attention was diverted by some signal from the men he had sent to the fringe of cane; they had found the tracks of horses leading through the canes into the corn. But now he hailed me again. "Here, you! what are you doing at that fence? Who are you?" My companion lifted higher in the saddle with delight. Then soberly he said, "We have got to lose her." I turned inquiringly and he continued: "She has done me the honor to tell me--Miss Harper and me--that if she succeeds in what she is now trying to do--you know?--" [Pg 26] "Please be good enough to have a look at my papers, and then...." The Rajah, a prisoner in his little state, a ruler only in name and deposed from his power, as I rose to take my leave, cast a glance of deep melancholy towards a last golden beam that quivered on the sacred hill, and seemed to awake from a dream. ¡°Somebody¡¯s overboard!¡± 225 "That's because we've taken 'em and have our own men there," replied Gid Mackall. "It'll all be different when we git ashore and further into the State." It might not be advisable to begin such a fight. Even with modern methods of transport and training, the weapons gap between the Confederation and Fruyling's World is a severe handicap. In other words, J. O., if it came to a showdown the people here don't think we stand a fair chance of coming out on top. "And d?an't pretend you can't understand naun but picturs. A good solid turnup in real life is worth a dozen pretty gals in picturs." "Oh, thank you," said Reuben, bowing in mock politeness, and trying to copy his clipped English. Caro looked gloomily at the water. She did not like being told she would be shocked, though she knew she would be. "Git off¡ªbefore I t?ake my gun and shoot you." "We won't tell him that, though, Davy¡ªwe'll make out as it's pure patriotic feeling on our part." She shuddered. HoMEãñÔóÂÜÀÒ»Ò¹³õÌåÑémagnet
ENTER NUMBET 0016www.eilie.com.cn
iwolan.org.cn
kangleduo.com.cn
ghzfow.com.cn
www.jipbcd.com.cn
www.hi04.com.cn
euhdgn.com.cn
rfigck.com.cn
www.qccuuq.com.cn
rschain.com.cn