Embedded Files
DATABASE
It is a collection of information in form of tables it can easily be accessed, managed, and updated.
A database management system (DBMS) is system software for creating and managing databases.
A DBMS makes it possible for end users to create, read, update and delete data in a database.
DBMS is a software program that is used to create and manage databases effectively and efficiently.
The database system stores data centrally at one place where all applications can get data required. Its advantages :
1. Reduce redundancy 2. Enhance data security 3.Reduce inconsistency 4. Sharing data.
SQL COMMANDS / QUERIES
1. VIEW EXISTING DATABASE
To view existing database names, the command is : SHOW DATABASES ;
2. CREATING DATABASE IN MYSQL
For creating the database in MySQL, we write the following command :
CREATE DATABASE <databasename> ;
e.g. In order to create a database Student, command is :
CREATE DATABASE Student ;
3. ACCESSING DATABASE
For accessing already existing database , we write :
USE <databasename> ;
e.g. to access a database named Student , we write command as :
USE Student ;
4. DELETING DATABASE
For deleting any existing database , the command is :
DROP DATABASE <databasename> ;
e.g. to delete a database , say student, we write command as ;
DROP DATABASE Student ;
5. VIEWING TABLE IN DATABASE
In order to view tables present in currently accessed database , command is :
SHOW TABLES ;
CREATE TABLE:
CREATE TABLE employee
(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;
Inserting record:
INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000);
SELECT Records
To select only ENAME, GRADE and GROSS column from student table
SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;
to retrieve everything (all columns) from a table student/Select all records from tabl :
SELECT * FROM student ;
To display details of employee whose name ends with ‘y’.
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;
To display names of employee whose name starts with R in EMPLOYEE table, the command is :
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;
To display details of employee whose second character in name is ‘e’
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ;
To display details of employee whose grade value is A1 or A2 :
SELECT * FROM EMPLOYEE WHERE GRADE IN (‘A1’ , ‘A2’);
To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:
SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;
To display the details of employees in EMPLOYEE table in alphabetical order, we use command :
SELECT * FROM EMPLOYEE ORDER BY ENAME ;
Display list of employee in descending alphabetical order whose salary is greater than 40000.
SELECT ENAME FROM EMPLOYEE
WHERE GROSS > 40000 ORDER BY ENAME desc ;
UPDATE Records
Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000.
UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;
Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.
UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1009 ;
Double the salary of employees having grade as ‘A1’ or ‘A2’ .
UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;
DELETE RECORDS
To remove the details of those employee from EMPLOYEE table whose grade is A1.
DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;
To delete all records from employee table :
DELETE FROM EMPLOYEE ;
ADD COLUMN
To add a new column ADDRESS to the EMPLOYEE table, we can write command as :
ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);
In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2).
ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);
In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).
ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);
DELETE COLUMN
To delete column GRADE from table EMPLOYEE, we will write :
ALTER TABLE EMPLOYEE DROP GRADE ;
Group By : clause used to create group of records from a table
Assignment:
Computer based record keeping system called .......
Duplication of data is called.......
Multiple mismatch copies of same data is called .........
A field that uniquely identify record in a table called.........
An interface is user defined layout that lets users can view, view ,enter or change data in tables, know as ...........
Named collection of fields which represent a complete unit of information is called ......
A statement that gives you filtered data according to your condition and specification , is called
A row in a table is called.....
When a primary key contains more than one filed , it is know as...........
........holds a single piece of data.
Ans 1. DBMS 2. data redundancy 3. data inconsistancy 4.Primary key 5. form 6. record 7. Query 8. tuple 9. Comosite primary key 10. Field or attribute or column
JOINS
A join is a query that combines rows from two or more tables. In a join- query, more than
one table are listed in FROM clause.
This query will give you the Cartesian product i.e. all possible concatenations are formed of all rowsof both the tables EMPL and DEPT. Such an operation is also known as Unrestricted Join. It returns
n1 x n2 rows where n1 is number of rows in first table and n2 is number of rows in second table.
It returns c1+c2 columns where c1 is numbers of columns in first table and c2 is number of columns in second table
EQUI-JOIN
- The join in which columns are compared for equality, is called Equi - Join. In equi-join, all the
columns from joining table appear in the output even if they are identical.
e.g. SELECT * FROM empl, dept
WHERE empl.deptno = dept.deptno ;
Q: with reference to empl and dept table, find the location of employee SMITH.
ename column is present in empl and loc column is present in dept. In order to obtain the result, we
have to join two tables.
SELECT ENAME, LOC
FROM EMPL, DEPT
WHERE EMPL.DEPTNO = DEPT.DEPTNO AND ENAME=’SMITH’;
TABLE ALIAS
- A table alias is a temporary label given along with table name in FROM clause.
e.g.
SELECT E.DEPTNO, DNAME,EMPNO,ENAME,JOB,SAL
FROM EMPL E, DEPT D
WHERE E.DEPTNO = DEPT.DEPTNO
ORDER BY E.DEPTNO;
In above command table alias for EMPL table is E and for DEPT table , alias is D.
Q: Display details like department number, department name, employee number, employee
name, job and salary. And order the rows by employee number with department number. These
details should be only for employees earning atleast Rs. 6000 and of SALES department.
SELECT E.DEPTNO, DNAME,EMPNO, ENAME, JOB, SAL
FROM EMPL E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND DNAME=’SALES’
AND SAL>=6000
ORDER BY E.DEPTNO;
NATURAL JOIN
By default, the results of an equijoin contain two identical columns. One of the two identical
columns can be eliminated by restating the query. This result is called a Natural join.
e.g. SELECT empl.*, dname, loc
FROM empl,dept
WHERE empl.deptno = dept.deptno ;
empl.* means select all columns from empl table. This thing can be used with any table.
The join in which only one of the identical columns(coming from joined tables) exists, is called
Natural Join.
SQL Queries
Creating table:
CREATE TABLE employee
(ECODE int , ENAME varchar(20) , GENDER char(1) , GRADE char(2) , GROSS int ) ;
Inserting record:
INSERT INTO employee VALUES(1001 , ‘Ravi’ , ‘M’ , ‘E4’ , 50000);
To select only ENAME, GRADE and GROSS column from student table
SELECT ENAME , GRADE , GROSS FROM EMPLOYEE ;
to retrieve everything (all columns) from a table student/Select all records from tabl :
SELECT * FROM student ;
To display details of employee whose name ends with ‘y’.
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘%y’ ;
To display names of employee whose name starts with R in EMPLOYEE table, the command is :
SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE ‘R%’ ;
To display details of employee whose second character in name is ‘e’
SELECT * FROM EMPLOYEE WHERE ENAME LIKE ‘_e%’ ;
To display details of employee whose grade value is A1 or A2 :
SELECT * FROM EMPLOYEE WHERE GRADE IN (‘A1’ , ‘A2’);
To display ECODE, ENAME and GRADE of those employees whose salary is between 40000 and 50000, command is:
SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS BETWEEN 40000 AND 50000 ;
To display the details of employees in EMPLOYEE table in alphabetical order, we use command :
SELECT * FROM EMPLOYEE ORDER BY ENAME ;
Display list of employee in descending alphabetical order whose salary is greater than 40000.
SELECT ENAME FROM EMPLOYEE
WHERE GROSS > 40000 ORDER BY ENAME desc ;
Change the salary of employee in EMPLOYEE table having employee code 1009 to 55000.
UPDATE EMPLOYEE SET GROSS = 55000 WHERE ECODE = 1009 ;
Update the salary to 58000 and grade to B2 for those employee have employee code is 1001.
UPDATE EMPLOYEE SET GROSS = 58000, GRADE=’B2’ WHERE ECODE = 1009 ;
Double the salary of employees having grade as ‘A1’ or ‘A2’ .
UPDATE EMPLOYEE SET GROSS = GROSS * 2 WHERE GRADE=’A1’ OR GRADE=’A2’ ;
To remove the details of those employee from EMPLOYEE table whose grade is A1.
DELETE FROM EMPLOYEE WHERE GRADE =’A1’ ;
To delete all records from employee table :
DELETE FROM EMPLOYEE ;
To add a new column ADDRESS to the EMPLOYEE table, we can write command as :
ALTER TABLE EMPLOYEE ADD ADDRESS VARCHAR(50);
In table EMPLOYEE , change the datatype of GRADE column from CHAR(2) to VARCHAR(2).
ALTER TABLE EMPLOYEE MODIFY GRADE VARCHAR(2);
In table EMPLOYEE , change the column ENAME to EM_NAME and data type from VARCHAR(20) to VARCHAR(30).
ALTER TABLE EMPLOYEE CHANGE ENAME EM_NAME VARCHAR(30);
To delete column GRADE from table EMPLOYEE, we will write :
ALTER TABLE EMPLOYEE DROP GRADE ;
Group By : clause used to create group of records from a table
Page updated
Google Sites
Report abuse