Vorgurakendused 1 SQL naited loengust

mysql --user=rstudent --password=student reddit

NB! Kui loengus ei olnud, kysi parooli ja kasutajatunnust loengus/praxis!

create database reddit; -- seda ei saa sa oma õigustega teha, on juba tehtud

show databases;

use reddit;

show tables;

describe <table>;

drop <table>;

create table t1234_users(
username VARCHAR(30),
name VARCHAR(40),
sex char(1),
various VARCHAR(200),
PRIMARY KEY (username));

id INT NOT NULL AUTO_INCREMENT

INSERT INTO t1234_users 
(username,name,sex) VALUES('tanel', 'Tanel Tammet', 'M');
INSERT INTO tammet_users 
(username,name,sex) VALUES('kaire', 'Kaire Kass', 'F');
INSERT INTO tammet_users 
(username,name,sex) VALUES('mary', 'Mary Black', 'F');
INSERT INTO tammet_users 
(username,name,sex) VALUES('john', 'John Smith', 'M');
INSERT INTO tammet_users 
(username,name,sex) VALUES('william', 'William Blazkowicz', 'M');

mysql --user=... --password=... reddit < proov.sql 

create table t1234_friends(
id INT NOT NULL AUTO_INCREMENT,
inviter VARCHAR(30),
invitee VARCHAR(30),
status char(1) default 'I',
PRIMARY KEY (id));

insert into t1234_friends
(inviter,invitee) values ('tanel','kaire');
insert into tammet_friends
(inviter,invitee) values ('tanel','mary');

select * from t1234_friends;

select t1234_users.name,u2.name,u2.sex from 
t1234_users,t1234_users u2,t1234_friends where 
t1234_users.username=t1234_friends.inviter and 
u2.username=t1234_friends.invitee;

create table t1234_users(
username VARCHAR(30) NOT NULL DEFAULT NULL,
name VARCHAR(40) NOT NULL DEFAULT NULL,
sex char(1),
various VARCHAR(200),
PRIMARY KEY (username));


show  engines;