BASIC QUERIES
1- distinct- to return different values [ select distinct id, name from table ]
2- where- to filter records
3- And/Or –
(i) AND =when both conditions are true
(ii) OR= when any one condition is true.
4- orderBy- used to sort the result set [ ASC | DESC ]
5- select top- number of records to be returned
Mysql– select * from user limit 5;
Oracle – select * from user where rownum<=5;
6- like- used in where clause to search for a pattern in a column
7- wildcards- used with like operator to search for data
1- % = for 0 or more chars
2- _ = for a single char
3- [charlist] = sets and ranges of chars to match
8- In clause- used to include multiple values in where clause
9- between – used with where clause, to select values within a range
10- alias– used to provide temporary names
11- union – used to combine result set of two or more select statements
12- select into – used to copy data from 1 table to another
select * into newtable in ‘a.mdb’ from oldtable;
13- insert into select – copies data from 1 table and inserts into existing table
Insert into table2 select * from table 1
14- create table-
Create table tn ( cn int(10), cn1 String(45)):
15- constraints- used to set rules for data in table
i- unique– unique value
ii- not null – can’t store null
iii- primary key – combination of not null and unique,
It helps to find particular record in a table
iv- foreign key- used to refer integrity of data in one table to match values in another table
– foreign key in one table points to primary key in another table
Mysql-
Create table tn ( cn int(10), roomno. int(45), primary key (cn), foreign key (roomno) references person(roomno));
Others–
Create table tn ( cn int(10) primary key, roomno. int(45) foreign key references person(roomno));
5- check- used to check if column meets a specific condition
6- default– used to set default value for a column
i.e – Create table tn ( cn int(10) unique, cn1 String(45)):
16- indexes- used to find data more quickly and efficiently.
* slow in updation
Create index statement is used.
-create index P index on persons (LastName)
17- drop vs truncate-
Drop deletes table, truncate deletes data
18- SQL JOIN- these are used to combine rows from two or more tables.
A- inner join(simple)-
Combines rows if common field is found.
i.e- select * from table1 inner join table2 on table1.id=table2.id
B- Left join-
Returns all rows from left table1, with matching rows in right table2
-returns null in left side if there is no match.
C- Right join-
Returns all rows from right table2, with matching rows in left tables1
– returns null is right side if there is no match
D- full join-.
Returns all rows from both tables whether there is a match or not.
19- keys–
a- Primary key- used to identifies each row in a database.
b- composite key- set of more than 1 column, used to identifies each row, together. Can’t work individually
c- foreign key- used to identifies rows in another table.