Primary Key

Type Syntax Example

At time of creating table
CREATE TABLE table_name create table emp
(col-1 datatype (size) primary key, (empid int primary key,
Column level
col-2 datatype (size), name varchar(20));
(without constraint name)
col-3 datatype (size) alter table emp
…….); drop constraint emp_pkey;
CREATE TABLE table_name create table emp
(col-1 datatype (size) constraint (empid int constraint a primary key,
Column level constraint_name primary key, name varchar (20));
(with constraint name) col-2 datatype (size),
col-3 datatype (size) alter table emp
…….); drop constraint a;
CREATE TABLE table_name create table emp
(col-1 datatype (size), (empid int,
End of syntax col-2 datatype (size), name varchar(20),
(without constraint name) col-3 datatype (size) salary int,
……. primary key (empid,name));
Primary key (col-1,col-2));
CREATE TABLE table_name create table emp
(col-1 datatype (size), (empid int,
col-2 datatype (size), name varchar(20),
End of syntax
col-3 datatype (size) salary int,
(with constraint name)
……. constraint a primary key
Constraint a Primary key (col-1,col- (empid,name));
2));
After creating table
ALTER TABLE table_name alter table emp
Add primary key (col-1,col-2); add primary key (empid);
After creating table
alter table emp
add constraint a primary key
(empid);

With primary key

With primary key (empid)

insert into emp (name)
values ('b');
ERROR: null value in column "empid" of relation "emp" violates not-null constraint DETAIL: Failing row
contains (null, b, null). SQL state: 23502

insert into emp (empid)

values (2);
 Foreign Key
Syntax
CREATE TABLE table_name
(col-1 datatype (size) primary key,
col-2 datatype (size),
col-3 datatype (size),
CONSTRAINT constraint_name FOREIGN KEY (fk_col)
REFERENCES <parent_table> (parent_table_col));

At the time of creating Table

Create table 1 Create table 2
create table dept create table emp
(d_id int primary key, (empid int primary key,
d_name varchar (20)); ename varchar(20),
d_id int,
constraint a foreign key (d_id)
references dept (d_id));
After Creating table
ALTER TABLE table_name alter table emp
ADD CONSTRAINT constraint_name add constraint did_fkey foreign key (d_id)
FOREIGN KEY (fk_col) references dept (d_id);
REFERENCES <parent_table> (parent_table_col)
 UNIQUE
Syntax Example
CREATE TABLE table_name CREATE table emp
(col-1 datatype (size), (empid int,
col-2 datatype (size) UNIQUE, name varchar (20) unique);
col-3 datatype (size), alter table emp
…….); drop constraint emp_name_key;
CREATE TABLE table_name create table emp1
(col-1 datatype (size), (empid int,
col-2 datatype (size), name varchar (20),
col-3 datatype (size), unique (empid,name));
UNIQUE (col-1,col-2));
ALTER TABLE table_name alter table emp
Add unique (col-1,col-2); add unique (empid);
NOTnull
Syntax Example
At the time of creating Table
CREATE TABLE table_name CREATE table emp
(col-1 datatype (size), (empid int,
col-2 datatype (size) not null, name varchar (20) not null);
col-3 datatype (size),
…….);
After Creating table
ALTER TABLE table_name alter table emp
ALTER COLUMN column_name set not alter column name set not null;
null;
 Check
Syntax Example
At the time of creating Table
CREATE TABLE table_name create table emp2
(col-1 datatype (size) primary key, (id serial primary key,
col-2 datatype (size) unique, name varchar unique,
col-3 datatype (size) check (condition), salary numeric check (salary > 0));
…….);

insert into emp2

values (2,'b',-10);
ERROR: new row for relation "emp2" violates check constraint "emp2_salary_check" DETAIL: Failing row contains (2, b, -10).

insert into emp2

values (2,'a',1000);
ERROR: duplicate key value violates unique constraint "emp2_pkey" DETAIL: Key (id)=(2) already exists
After Creating table
ALTER TABLE table_name alter table student1
ADD CONSTRAINT constraint_name add primary key (sid),
Check (condition); add unique (name),
add constraint age_check
check (age>10);
 Aggregate Functions

1. AVG
Syntax:
SELECT AVG(column_name) from table_name WHERE CONDITION;
E.g.
select avg(amount) as "average Amount" from payment;
E.g.
select avg(amount) as "average Amount" from payment where customer_id=341;
2. MAX
E.g.
select max(amount) as "Max amount" from payment;
select max(amount) as "Max amount" from payment where customer_id=341;
3. MIN
E.g.
select min(amount) as "Min amount" from payment;
select min(amount) as " Min amount" from payment where customer_id=341;
4. SUM
E.g.
select sum(amount) as "Total Amount" from payment;
select sum(amount) as " Total Amount " from payment where customer_id=341;
 5. COUNT
E.g.
select count(*) as "total tuples" from payment;
select count(customer_id) as "count cust_id" from payment where customer_id=341;