Untitled

last=# \d
Did not find any relations.
last=# create type genders enum('male', 'female');
ERROR:  syntax error at or near "enum"
LINE 1: create type genders enum('male', 'female');
                            ^
last=# create type genders as enum('male', 'female');
CREATE TYPE
last=# create type student_info as (address text, email text);
CREATE TYPE
last=# create table student (name text, s_info student_info, gender genders, id serial primary key);
CREATE TABLE
last=# insert into student values ('mohamed', row('cairo', 'mo@y.c'), 'male');
INSERT 0 1
last=# insert into student values ('aya', row('alexandria', 'aya@y.c'), 'female');
INSERT 0 1
last=# insert into student values ('ahmed', row('tanta', 'am@y.c'), 'male');
INSERT 0 1
last=# alter table student add column birth date;
ALTER TABLE
last=# update student set birth = '1990-1-5' where id = 1;
UPDATE 1
last=# update student set birth = '1998-10-9' where id = 2;
UPDATE 1
last=# update student set birth = '2000-11-18' where id = 3;
UPDATE 1
last=# select * from student;
  name   |        s_info        | gender | id |   birth
---------+----------------------+--------+----+------------
 mohamed | (cairo,mo@y.c)       | male   |  1 | 1990-01-05
 aya     | (alexandria,aya@y.c) | female |  2 | 1998-10-09
 ahmed   | (tanta,am@y.c)       | male   |  3 | 2000-11-18
(3 rows)

last=# create table subject (sub_id serial primary key, name text not null, id int references student(id) on update cascade on delete set null);
CREATE TABLE
last=# select * from student;
  name   |        s_info        | gender | id |   birth
---------+----------------------+--------+----+------------
 mohamed | (cairo,mo@y.c)       | male   |  1 | 1990-01-05
 aya     | (alexandria,aya@y.c) | female |  2 | 1998-10-09
 ahmed   | (tanta,am@y.c)       | male   |  3 | 2000-11-18
(3 rows)

last=# isnert into student values ('aly', row(saini, 'aly@y.c'), 'male', 4, '1980-1-1');
ERROR:  syntax error at or near "isnert"
LINE 1: isnert into student values ('aly', row(saini, 'aly@y.c'), 'm...
        ^
last=# insert into student values ('aly', row(saini, 'aly@y.c'), 'male', 4, '1980-1-1');
ERROR:  column "saini" does not exist
LINE 1: insert into student values ('aly', row(saini, 'aly@y.c'), 'm...
                                               ^
last=# insert into student values ('aly', row('saini', 'aly@y.c'), 'male', 4, '1980-1-1');
INSERT 0 1
last=# insert into student values ('rewan', row('tanta', 're@y.c'), 'female', 5, '1988-1-1');
INSERT 0 1
last=# select * from student;
  name   |        s_info        | gender | id |   birth
---------+----------------------+--------+----+------------
 mohamed | (cairo,mo@y.c)       | male   |  1 | 1990-01-05
 aya     | (alexandria,aya@y.c) | female |  2 | 1998-10-09
 ahmed   | (tanta,am@y.c)       | male   |  3 | 2000-11-18
 aly     | (saini,aly@y.c)      | male   |  4 | 1980-01-01
 rewan   | (tanta,re@y.c)       | female |  5 | 1988-01-01
(5 rows)

last=# insert into student values ('amr', row('aswan', 'amr@y.c'), 'male', 6, '2005-9-8');
INSERT 0 1
last=# select * from student;
  name   |        s_info        | gender | id |   birth
---------+----------------------+--------+----+------------
 mohamed | (cairo,mo@y.c)       | male   |  1 | 1990-01-05
 aya     | (alexandria,aya@y.c) | female |  2 | 1998-10-09
 ahmed   | (tanta,am@y.c)       | male   |  3 | 2000-11-18
 aly     | (saini,aly@y.c)      | male   |  4 | 1980-01-01
 rewan   | (tanta,re@y.c)       | female |  5 | 1988-01-01
 amr     | (aswan,amr@y.c)      | male   |  6 | 2005-09-08
(6 rows)

last=# select * from student where gender = 'male';
  name   |     s_info      | gender | id |   birth
---------+-----------------+--------+----+------------
 mohamed | (cairo,mo@y.c)  | male   |  1 | 1990-01-05
 ahmed   | (tanta,am@y.c)  | male   |  3 | 2000-11-18
 aly     | (saini,aly@y.c) | male   |  4 | 1980-01-01
 amr     | (aswan,amr@y.c) | male   |  6 | 2005-09-08
(4 rows)

last=# select count('female') from student;
 count
-------
     6
(1 row)

last=# select name from student where birth < '1992-10-1';
  name
---------
 mohamed
 aly
 rewan
(3 rows)

last=# select name from student where birth < '1991-10-1';
  name
---------
 mohamed
 aly
 rewan
(3 rows)

last=# select name from student where birth < '1991-10-1' and gender = 'male';
  name
---------
 mohamed
 aly
(2 rows)

last=# \d
                 List of relations
 Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------
 public | student            | table    | postgres
 public | student_id_seq     | sequence | postgres
 public | subject            | table    | postgres
 public | subject_sub_id_seq | sequence | postgres
(4 rows)

last=# select * from subject;
 sub_id | name | id
--------+------+----
(0 rows)

last=# alter table subject add column max_score integer;
ALTER TABLE
last=# select * from subject;
 sub_id | name | id | max_score
--------+------+----+-----------
(0 rows)

last=# insert into subject (sub_id, name, id, max_score) values (1, 'cpp', 1, 80);
INSERT 0 1
last=# select * from subject;
 sub_id | name | id | max_score
--------+------+----+-----------
      1 | cpp  |  1 |        80
(1 row)

last=# insert into subject (sub_id, name, id, max_score) values (2, 'html', 2, 70);
INSERT 0 1
last=# select * from subject;
 sub_id | name | id | max_score
--------+------+----+-----------
      1 | cpp  |  1 |        80
      2 | html |  2 |        70
(2 rows)

last=# insert into subject (sub_id, name, id, max_score) values (3, 'css', 3, 90);
INSERT 0 1
last=# insert into subject (sub_id, name, id, max_score) values (4, 'c#', 4, 50);
INSERT 0 1
last=# insert into subject (sub_id, name, id, max_score) values (3, 'css', 3, 90);
ERROR:  duplicate key value violates unique constraint "subject_pkey"
DETAIL:  Key (sub_id)=(3) already exists.
last=# select * from subject;
 sub_id | name | id | max_score
--------+------+----+-----------
      1 | cpp  |  1 |        80
      2 | html |  2 |        70
      3 | css  |  3 |        90
      4 | c#   |  4 |        50
(4 rows)

last=# select name, max_score from subject;
 name | max_score
------+-----------
 cpp  |        80
 html |        70
 css  |        90
 c#   |        50
(4 rows)

last=# select subject having max_score = MAX(max_score);
ERROR:  column "subject" does not exist
LINE 1: select subject having max_score = MAX(max_score);
               ^
last=# select max_score from subject having max_score = MAX(max_score);
ERROR:  column "subject.max_score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select max_score from subject having max_score = MAX(max_sco...
               ^
last=# select max_score from subject ;
 max_score
-----------
        80
        70
        90
        50
(4 rows)

last=# select max_score from subject group by name having max_score = MAX(max_score);
ERROR:  column "subject.max_score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select max_score from subject group by name having max_score...
               ^
last=# select name, max_score from subject group by name having max_score = MAX(max_score);
ERROR:  column "subject.max_score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name, max_score from subject group by name having max...
                     ^
last=# select name, (max_score) from subject group by name having max_score = MAX(max_score);
ERROR:  column "subject.max_score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name, (max_score) from subject group by name having m...
                      ^
last=# select name from subject group by name having max_score = MAX(max_score);ERROR:  column "subject.max_score" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name from subject group by name having max_score = MA...
                                                      ^
last=# select name, max_score from subject group by max_score having max_score = MAX(max_score);
ERROR:  column "subject.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name, max_score from subject group by max_score havin...
               ^
last=# select max_score from subject group by max_score having max_score = MAX(max_score);
 max_score
-----------
        70
        80
        90
        50
(4 rows)

last=# select subject where max_score = MAX(max_score);
ERROR:  column "subject" does not exist
LINE 1: select subject where max_score = MAX(max_score);
               ^
last=# select name from subject where max_score = MAX(max_score);
ERROR:  aggregate functions are not allowed in WHERE
LINE 1: select name from subject where max_score = MAX(max_score);
                                                   ^
last=# select name from subject where max_score = select(MAX(max_score) from subject);
ERROR:  syntax error at or near "select"
LINE 1: select name from subject where max_score = select(MAX(max_sc...
                                                   ^
last=# select name from subject where max_score = select(MAX(max_score) from subject);
ERROR:  syntax error at or near "select"
LINE 1: select name from subject where max_score = select(MAX(max_sc...
                                                   ^
last=# select name from subject where max_score = select(MAX(max_score) from subject;);
ERROR:  syntax error at or near "select"
LINE 1: select name from subject where max_score = select(MAX(max_sc...
                                                   ^
last=# select name from subject where max_score = (select MAX(max_score) from subject);
 name
------
 css
(1 row)

last=# select name from, max_score subject where max_score = (select MAX(max_score) from subject);
ERROR:  syntax error at or near ","
LINE 1: select name from, max_score subject where max_score = (selec...
                        ^
last=# select name, max_score from subject where max_score = (select MAX(max_score) from subject);
 name | max_score
------+-----------
 css  |        90
(1 row)

last=# select name from student where name like 'a%';
 name
-------
 aya
 ahmed
 aly
 amr
(4 rows)

last=# select count(name) from student where name = 'mohamed';
 count
-------
     1
(1 row)

last=# select count(gender) from student where gender = 'male';
 count
-------
     4
(1 row)

last=# select count(gender) from student where gender = 'female';
 count
-------
     2
(1 row)

last=# select name, count(*) from student group by name;
  name   | count
---------+-------
 rewan   |     1
 ahmed   |     1
 aya     |     1
 aly     |     1
 mohamed |     1
 amr     |     1
(6 rows)

last=# select name, count(*) from student group by name having count(*) > 2;
 name | count
------+-------
(0 rows)

last=# insert into student values ('aly', row('saini', 'aly@y.c'), 'male', 4, '1980-1-1');
ERROR:  duplicate key value violates unique constraint "student_pkey"
DETAIL:  Key (id)=(4) already exists.
last=# insert into student values ('aly', row('saini', 'aly@y.c'), 'male', 6, '1980-1-1');
ERROR:  duplicate key value violates unique constraint "student_pkey"
DETAIL:  Key (id)=(6) already exists.
last=# select * from student;
  name   |        s_info        | gender | id |   birth
---------+----------------------+--------+----+------------
 mohamed | (cairo,mo@y.c)       | male   |  1 | 1990-01-05
 aya     | (alexandria,aya@y.c) | female |  2 | 1998-10-09
 ahmed   | (tanta,am@y.c)       | male   |  3 | 2000-11-18
 aly     | (saini,aly@y.c)      | male   |  4 | 1980-01-01
 rewan   | (tanta,re@y.c)       | female |  5 | 1988-01-01
 amr     | (aswan,amr@y.c)      | male   |  6 | 2005-09-08
(6 rows)

last=# insert into student values ('aly', row('saini', 'aly@y.c'), 'male', 7, '1980-1-1');
INSERT 0 1
last=# insert into student values ('aly', row('saini', 'aly@y.c'), 'male', 8, '1980-1-1');
INSERT 0 1
last=# select name, count(*) from student group by name having count(*) > 2;
 name | count
------+-------
 aly  |     3
(1 row)

last=#