-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL stuff
119 lines (89 loc) · 3.05 KB
/
SQL stuff
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
UBCREATE TABLE actors (
id SERIAL primary key,
name VARCHAR(255) not null,
age VARCHAR(255),
numberofoscars VARCHAR(255)
);
INSERT INTO actors (name, age, numberofoscars) VALUES ('Leonardo DiCaprio', 41, 1);
INSERT INTO actors (name, age, numberofoscars) VALUES ('Jennifer Lawrence', 25, 1);
INSERT INTO actors (name, age, numberofoscars) VALUES ('Samuel L. Jackson', 67, 0);
INSERT INTO actors (name, age, numberofoscars) VALUES ('Meryl Streep', 66, 3);
INSERT INTO actors (name, age, numberofoscars) VALUES ('John Cho', 43, 0);
ALTER TABLE actors ALTER COLUMN numberofoscars ;
ALTER TABLE actors ALTER COLUMN numberofoscars TYPE integer USING numberofoscars::integer;
SELECT * FROM actors WHERE numberofoscars > 0
ALTER TABLE actors ALTER COLUMN age TYPE integer USING age::integer;
;
ALTER TABLE signatures ADD userId INTEGER NOT NULL
SELECT TOP 0 * FROM actors;
SELECT * FROM actors WHERE 1=0;
DELETE FROM actors WHERE id > 0;
SELECT TOP 10 *
INTO #TempTable
FROM actors;
EXEC tempdb.dbo.sp_help N'#TempTable';
CREATE TABLE signatures (
id SERIAL PRIMARY KEY,
user_id integer REFERENCES users (id) NOT NULL,
signature TEXT NOT NULL
);
id (the primary key), first name, last name, email address, and the hashed password.
DROP TABLE users;
DROP TABLE signatures;
DROP TABLE user_profiles;
CREATE TABLE users (
id SERIAL PRIMARY KEY,
FirstName VARCHAR(300) NOT NULL,
LastName VARCHAR(300) NOT NULL,
Email VARCHAR(300) UNIQUE,
HashPass VARCHAR(320) NOT NULL,
facebook_id VARCHAR(300),
pic_url VARCHAR(500),
link VARCHAR(300),
DateCreated timestamptz NOT NULL
CONSTRAINT email_facebook_notnull CHECK (
(
Email IS NOT NULL
AND facebook_id IS NULL
)
OR (
Email IS NULL
AND facebook_id IS NOT NULL
)
OR (
Email IS NOT NULL
AND facebook_id IS NOT NULL
)
)
);
CREATE TABLE signatures (
id SERIAL PRIMARY KEY,
user_id integer REFERENCES users (id) NOT NULL,
signature TEXT NOT NULL
);
CREATE TABLE user_profiles (
id SERIAL PRIMARY KEY,
user_id integer REFERENCES users (id) NOT NULL CONSTRAINT user_id_unique UNIQUE,
age integer,
city VARCHAR(300),
url VARCHAR(300)
);
ALTER TABLE users ADD CONSTRAINT uniqueEmails UNIQUE (Email);
INSERT INTO signatures (first, last, signature) VALUES (Brian, Mullin, Infinite);
INSERT INTO signatures (first, last, signature) VALUES ('Brian', 'Mullin', 'Infinite'
);
SELECT
*
FROM
signatures
INNER JOIN user_profiles ON signatures.userId = user_profiles.user_id;
SELECT users.id, user_profiles.city, user_profiles.age, user_profiles.url, users.FirstName, users.LastName, users.Email FROM user_profiles, signatures, users WHERE user_profiles.user_id = users.id and users.id = signatures.userId;
ALTER TABLE users ADD facebook_id VARCHAR(300);
ALTER SEQUENCE users_id_seq RESTART WITH 1;
ALTER SEQUENCE signatures_id_seq RESTART WITH 1;
ALTER SEQUENCE user_profiles_id_seq RESTART WITH 1;
DELETE FROM users *;
DELETE FROM signatures *;
DELETE FROM user_profiles *;
DROP TABLE users, signatures, user_profiles;
ALTER TABLE signatures ADD CONSTRAINT REFERENCES users (id)