Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

In this tutorial we will use the well-known North wind sample database (included in MS Access and MS SQL Server).

Below is a selection from the “Company” table


Starting SQL Query

Q.No: How to make database?
Solution: Create database emp;

Q.No: How to make table?
Solution: Create table company (ID int(4), NAME varchar(20), ADDRESS varchar (20), SALARY int(50), Contact_Number int(10);

ALTER COMMAND

  • When structure change in table then we will be use alter command

Alter:-
(i) Add Column
(ii) Drop Column
(iii) Change datatype/datasize


ADD COLUMN

Q.No: How to add Column in table?
Solution: alter table company add Contact_Number int(10);


DELETE / DROP COLUMN

Q.No: How to delete Column in table?
Solution: alter table company drop Contact_Number;


CHANGE DATATYPE / DATASIZE

Q.NO: How to change Column datatype and datasize
Solution: alter table company modify column ADDRESS varchar(6);


UPDATE TABLE

Q.No: How to Update table?
Solution: update company set ADDRESS=’UttarPradesh’ where ID=’5′;

Q.No: How to Add row Information in table?
Solution: insert into company values(7,”Diblu”,69,”bhadsiyana”,10000,9876543210);


DELETE ROWS

Q.No: How to delete rows information in a table
Solution: delete from company where ID=’7’ and NAME=”Diblu”;


LIKE Operators

Q.No: How to find the Address where address start with m?
Solution: select * from company where ADDRESS like ‘m%’;

Q.No: How to find Address end with i?
Solution: select * from company where ADDRESS like ‘%i’;


WILDCARDS

Q.No: How to find Address starting with “d” followed by any character followed by “l” followed by any character followed by “i”
Solution: select * from company where ADDRESS like ‘d_l_i’;

Q.No: How to find multiple values in a table where clause
Solution: select * from company where AGE in(’21’,’25’);


BETWEEN

Q.No: How to find the AGE within a range?
Solution: select * from company where AGE between 21 and 28;

Q.No: How to find the average salary of employee of AGE between 21 and 28?
Solution: select avg(salary) from company where AGE between 21 and 28

Q.No: Find the average salary of employee of job manager
Solution: select avg(sal) from cs where job=”manager”;

Q.No: How to find values not within a range?
Solution: select * from company where AGE not between 21 and 28;


MAXIMUM / MINIMUM

Q.No: How to find the minimum salary?
Solution: select min (salary) from company;

Q.No: How to find the total number of employee working in the company
Solution: select count (*) from company;

Q.No: How to find the maximum salary of employee working as a salesman?
Solution: select max(salary) from company where job=’salesman’; 

Q.No:  How to find the maximum salary of employee age as a 25?
Solution: select max(salary) from company where AGE=’25’;

Q.No: How to find the maximum salary of employee working in department number 10?
Solution: select max(salary) from company where Dept_No=’10’;

Q.No: Find the max salary of dept wise
Solution: select max(sal) from cs group by dept_no;

Q.No: How to find the 2nd largest salary?
Solution: select max(salary) from company where salary<(select max(salary) from company);


Group By

Q.No: How to find the address and number of employee in each address?
Solution: select address, count(*) from company group by address;


Having

  • It is used to restrict the data that is coming via group by.
  • It is used only with aggregate function.

Q.No: How to find the list avg salary of all the address having more than 3 employee
Solution: select address, avg(salary) from company group by address having count(*)<3;

Q.No: Find the employee name where name start with A
Solution: select ename from cs where ename like”a”;


Data Type SQL

Column Name Data Type Max Length
1. UserNo Int NULL
2. UserID Varchar 100
3. Password Varchar 50
4. FirstName Varchar 50
5. MiddleName Varchar 50
6. LastName Varchar 50
7. Title Varchar 10
8. EmailID Varchar 100
9. DeptID Int NULL
10. OrgID Int NULL
11. LocationID Int NULL
12. UserCategory Smallint NULL
13. CreationDate Datetime NULL
14. IsAdmin Bit NULL
15. LsActive Bit NULL
16. VerificationCode Varchar 50
17. Designation Varchar 50
18. AuthenticatCode Varchar 50

 

SQL Data Type Quick Reference

Data Type Access SQL Server Oracle MySQL PostgreSQL
Boolean Yes/No Bit Byte N/A Boolean
Integer Number (integer) Int Number Int Integer Int Integer
Float Number(single) Float Real Number Float Numeric
Currency Currency Money N/A N/A Money
String (fixed) N/A Char Char Char Char
String (variable) Text (<256) Memo (65k+) Varchar Varchar Varchar2 Varchar Varchar
Binary object OLE Object Memo Binary (fixed up to 8K) (<8K) Image (<2GB) Long Raw Blob Text Binary Varbinary

 

1 COMMENT

  1. Thank you for your blog post. Jones and I have been saving to get a new guide on this theme and your writing has made all of us to save all of our money. Your thoughts really responded to all our queries. In fact, greater than what we had thought of previous to the time we stumbled on your wonderful blog. I actually no longer nurture doubts as well as a troubled mind because you have completely attended to our needs above. Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here