Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 2
database --> stores collection of data,rows and cols -->cols-fields, rows-records
dbms database management system
rdbms relational database management system sql structured query language rdbms --> sql server,oracle
constraint - rules not null - that filed must be filled check - verify if all values statisfy the conditions default - automatically assigns values if no value has been specified for that field
unique - ensures unique values to be inserted into field
create table student (id int not null unique, name varchar(20), primary key (id)) alter table student add unique (id) alter table student add constraint pk_stud unique (id ,name)
index - helps for faster retrival of records
primary key - used to identify each record in a table
create table student (id int not null, name varchar(20), primary key (id)) alter table student add primary key(id)
create table student (id int not null, name varchar(20) not null) alter table student add constraint pk_stud primary key(id,name)
foreign key - ensures refrential intigrity
alter tabke student add foreign key (lib_id) references library(lib_id)
joins inner join - select * from stud_table inner join teacher_table; left outer join - select * from stud_table s left join teacher_table t on s.col = t.col; right outer join - select * from student s right join teacher t on s.col= t.col; full outer join - select * from student s full join teacher t on s.col=t.col;
self join - table - empp e_id e_name m_id 1 a null 2 b 1 3 c 1 4 d 2
select e.e_name as "name" , m.e_naame as 'manager' from empp e left join empp m on e.e_id = m.e_id
index - retrive records faster
create index inx on student(id,marks); drop index inx;
----------------------------------------------------------------------------------- -------------- Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically. The STATION table is described as follows:
ans------------------->>>>> select city,length(city) from station order by length(city) asc,city limit 1; select city,length(city) from station order by length(city) desc, city limit 1;
