SQL(Structured Query Language)

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.

1- inner join(simple)-
Combines rows if common field is found.
i.e- select * from table1 inner join table2 on table1.id=table2.id

2- Left join-
Returns all rows from left table1, with matching rows in right table2
-returns null in left side if there is no match.

3- Right join-
Returns all rows from right table2, with matching rows in left tables1
– returns null is right side if there is no match

4- 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s