Embedded Files
Online Classes available for different programming languages & for class IX (IT 402 Code), XII (IP 065)
MySQL DATABASE
Database is collection of related tables. It is stored in the software called DBMS (Database Management Software). Examples of DBMS are Microsoft Access, OPEN Office BASE, MySQL, etc.
Here we are going to store the data of a school.
(Example DATABASE NAME is: schooldb)
Table is collection rows and columns. We can create multiple tables in a database.
Here we are going to store the data of students in schooldb database.
(Example TABLE NAME is: student)
Command to create Database:
CREATE DATABASE schooldb;
Command to see all the list of databases in mysql
SHOW databases;
Command to change the current database
USE schooldb;
Command to see the list of tables in a current database
SHOW tables;
Different data type in mysql are as follows
int,char,varchar, decimal(10,2),date, time, datetime, money, image, byte.
Before creating table collect the following information:
1. identify table name
2. identify column Names
3. identify data type of each column
4. identify length of each column
5. identify columns with null (empty) and not null(not empty) values
6. identify which columns you want as primary key, foreign keys, unique key, default key and check key constraint
7. write the command to create the table
sample command to create a table is:
CREATE TABLE student
(
admno int(8) not null primary key,
name varchar(100) not null,
gender char(1),
passportno char(8),
dob date,
fee decimal(10,2)
);
How to see the structure of a table?
DESCRIBE TableName
DESC student;
How to add new column in a table?
ALTER TABLE student
add column classcode char(5);
How to modify existing column?
ALTER TABLE student
modify column classcode int ;
How to remove a column from a table?
ALTER TABLE student
DROP COLUMN classcode;
command to insert data in a table:
INSERT INTO student
VALUES(12345678,"jatinder","M","a123J456","19 77/06/20",12450.50);
command to see the data in a table
SELECT * FROM student;
In this command * means all columns
student is a table name
SELECT and FROM is a keywords which has special meaning
command to update data in a table:
UPDATE student
SET dob="1985/12/12"; → it will update all the rows
UPDATE student
SET dob="1985/12/12"
WHERE admno=12345678; → It will update on specific row
How to delete row or record from a table?
DELETE FROM student ; ----> it will remove all the rows from the table
DELETE FROM student
WHERE admno = 12345678; -----> it will remove only the specific record
How to remove data and structure of a table?
DROP TABLE student;
Important
All Capital Letters are Keywords
Example Commands for MySQL
Table Structure: loanaccounts
Q1. Write the command to create the database named ABCBank
Sol: Create database ABCBank;
Q2. Write the command to change the current database to ABCBank.
Sol: USE ABCBank;
Q3. Write the command to see all the tables in the current database abcbank.
Sol: show tables();
Q4. Write the command to create the table for the above data.
Sol:
create table loanaccounts
(
accountno int not null primary key,
customername varchar(50),
loanamount decimal(10,2),
installment int,
intrate decimal(5,2),
startdate date,
interest decimal(10,2)
);
Q5. Write the command to insert the following data in the loanaccounts table.
Sol:
insert into loanaccounts values(1,'Jatinder',300000,48,12.50,'2010-07-19',NULL);
insert into loanaccounts values(3 ,'Banta',300000,48,13.00,'2008-03-08',NULL);
insert into loanaccounts values(4,'Sandy',550000,36,NULL,'2010-12-06',NULL);
insert into loanaccounts values(5,'Mandy',600000,60,12.50,'2010-01-03',NULL);
insert into loanaccounts values(6 ,'John',700000,48,13.00,'2008-06-06',NULL);
insert into loanaccounts values(7 ,'Mohan',200000,60,NULL,'2009-03-05',NULL);
insert into loanaccounts values(8 ,'Ram',500000,36,11.50,'2010-12-06',NULL);
insert into loanaccounts values(9 ,'Sham',550000,33,12.00,'2008-03-08',NULL);
insert into loanaccounts values(10,'Gopal',800000,45,13.00,'2009-03-22',NULL);
Q6. Print the details of all the loan accounts.
Sol: Select * from loanaccounts;
Q7. Display the details of account number, account holder name and loan amount for all the loan accounts.
Sol: SELECT accountno,customername,loanamount FROM loanaccounts;
Q8. Display the details from all the loanaccounts whos installments are greater than 40.
Sol: SELECT * FROM loanaccounts WHERE installment > 40;
Q9. Display the details of account number, account holder name,installments and loan amount for all the loan accounts who has loan amount greater the 500000 and installment less then 55.
Sol: SELECT accountno, customername, loanamounts FROM loanaccounts WHERE loanamount>500000 AND installment<55;
Q10. Display the details of all the accounts where loan accounts has started after 2009-03-01
Sol: SELECT * FROM loanaccounts WHERE startdate > '2009-03-01';
Q11. Display all the details of all loan accounts where interest rate is null.
Sol: SELECT * FROM loanaccounts WHERE intrate is NULL;
Q12. Display all the details of loan accounts who has interest rate.
Sol: SELECT * FROM loanaccounts WHERE intrate is NOT NULL;
Q13. Print the details of all unique interest rates.
Sol: SELECT DISTINCT intrate FROM loanaccounts;
Q14. Display the details of all the loan accounts where installment are greater than 50 and the loan amount is less than 600000.
Sol: SELECT * FROM loanaccounts WHERE installment >50 AND loanamount<600000;
Page updated
Google Sites
Report abuse