MySQL

Question 1

What are DDL languages?

Answer 1

Data definition languages?

Create

Alter

Drop

Retrieve

Truncate

Comment.

These are used on tables

Question 2

What are DML languages?

Answer 2

Data Manipulation languages

Select

Insert

Update

Delete

these are used on the table’s data

Question 3

Query to see all databases on the database server

Answer 3

show databases;

Question 4

Query to tell the database server which database to work with

Answer 4

use database_name;

Question 5

How to delete a database?

Answer 5

drop database database_name;

Question 6

How to create a table?

Answer 6

create table table_name (column1_name datatype (size) null/not null/primary key,column2 column1_name datatype (size) null/not null,…..);

Question 7

to get all tables in a database

Answer 7

show tables;

Question 8

How to see the details and pattern of a table?

Answer 8

desc table_name;

this is short cut for

show columns from table_name;

Question 9

How to insert values into a table?

Answer 9

insert into table_name values (value1,value2,value3...)

Question 10

How to create a table with primary key?

Answer 10

If the table has more than one primary key then
.….. primary key(column name1,column name2);

mysql> create table train (pnr integer not null, train_num integer not null, nps
gr integer default 1, primary key (pnr,train_num));

mysql> create table train (pnr integer not null primary key, train_num integer not null primary key, npsgr integer default 1);

Question 11

How to insert multiple rows at once into the table?

Answer 11

insert into table_name values

(value11,value12,value13….),

(value 21,value22,value23);

Question 12

how to select a particular column/s from a table?

Answer 12

select column_name from tablename;

mysql> select pnr,train_num from train;

Question 13

What are constraints in Mysql

Answer 13

Not null

Unique

Primary key

Foreign key

Default

Check

Question 14

How to add a column to already created table?

Answer 14

Alter table table_name add column_name datatype constraints;

mysql> alter table train add (air_conditioning char default 1);
Query OK, 0 rows affected (0.77 sec)

Question 15

How to add acolumn after a specific column in an already created table?

Answer 15

mysql> alter table train add express char(10) after npsgr;

mysql> select * from train;
+—–+———–+——-+———+——————+
| pnr | train_num | npsgr | express | air_conditioning |
+—–+———–+——-+———+——————+
| 10 | 123 | 2 | NULL | 1 |
+—–+———–+——-+———+——————+
1 row in set (0.00 sec)

Question 16

How to make the column just inserted to be the first?

Answer 16

alter table train add doj date first;

mysql> alter table train add doj date first;

mysql> desc train;
+——————+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————+———-+——+—–+———+——-+
| doj | date | YES | | NULL | |
| pnr | int(11) | NO | PRI | NULL | |
| train_num | int(11) | NO | PRI | NULL | |
| npsgr | int(11) | YES | | 1 | |
| express | char(10) | YES | | NULL | |
| air_conditioning | char(1) | YES | | 1 | |
+——————+———-+——+—–+———+——-+
6 rows in set (0.02 sec)

Question 17

How to change the column name of a table?

Answer 17

mysql> alter table train change air_conditioning AC char(10);

alter table table_name change

^{<em>old_column_name new _column_name datatype(),</em>}

^{old_column_name new _column_name datatype(),}

^{old_column_name new _column_name datatype();}

Question 18

how to delete a column?

Answer 18

mysql> alter table train drop column AC;

alter table table_name drop column column_name;

notice since this drop action is on the table entities we use ‘drop’ not delete .

Question 19

Difference between modify and change in alter table command

Answer 19

Modify and change both are used in Alter table command.

‘change’ is used for renaming the column,changing data size etc.

‘Modify’ does everything as’ change’ except renaming the column name.

Question 20

How to delete the complete data of a table?

Answer 20

Truncate table table_name;

it drops the table and create a new table. faster way to the data in a table unlike delete.

Question 21

How to get first N records of a table?

Answer 21

Select * from table_name limit N;

Question 22

How to get N records after the record number S?

Answer 22

select * from table_name limit S,N

S is like offset,if s=10 and N =3 then we get record numbers 11,12,13

Question 23

Does the count of records in mysql strat from 1?

Answer 23

No starts from 0.

Question 24

Like operator

Answer 24

mysql> select * from train where train_num like ‘%2%’

gives anything which has 2 and starts and ends with anything.

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Question 25

In

not in

Answer 25

mysql> select * from train where 3 in (npsgr);

mysql> select * from train where 3 in (train_num,npsgr);(either or)

SELECT column_name(s)/*
FROM table_name
WHERE column_name IN (value1,value2,…);

Question 26

between and not between

Answer 26

is used for range. instead of a>3 & a<6

we can write

select * from table_name where column_name between 3 and 6;

Question 27

wild cards in mysql

Answer 27

wild cards are used with ‘like’ operator

%

_

[charlist]

[!charlist]

Question 28

How to output fields in uppercase/lowercase?

Answer 28

SELECT UPPER(col_name) FROM table_name;

Question 29

How to get the length of fields?

Answer 29

SELECT LENGTH(col_name) FROM table_name;

Question 30

How to output fields by reversing them?

Answer 30

SELECT REVERSE(col_name) FROM table_name:

Question 31

How to get a few of the characters from left/right?

Answer 31

SELECT LEFT(col_name,# of digits) FROM table_name;

Question 32

How to get a few characters from the middle of field?

Answer 32

SELECT MID(col_name,from_val,to_val) FROM table_name;

Question 33

How to get day/year/month part from a date field?

Answer 33

SELECT YEAR/(DAY/MONTH)(col_name) FROM table_name:

Question 34

How to MAKE A FIELD STORE BOTH DATE AND TIME?

Answer 34

MAKE THE DATATYPE OF THE FIELD ‘DATETIME’

Question 35

HOW TO OUTPUT A DATE FIELD IN OUR OWN FORMAT?

LIKE

Answer 35

SELECT DATE_FORMAT(col_name,’%y-%d-%m) FROM table_name (14-12-4)

SELECT DATE_FORMAT(col_name,’%Y-%d-%m) FROM table_name (2014-12-4)

SELECT DATE_FORMAT(col_name,’%Y-%D-%m) FROM table_name (2014-12th-4)

SELECT DATE_FORMAT(col_name,’%Y-%D-%M) FROM table_name (2014-12th-April)

Question 36

What is the default date format for a date field?

Answer 36

m/dd/yy

Question 37

Aliases for a column

Answer 37

SELECT col_name AS alias_name FROM table_name;

Question 38

ALIAS FOR TABLE NAME

Answer 38

SELECT col_name FROM table_name AS table_alias_name;

Question 39

CONCATENATE COLUMNS WITH ALIAS NAMES

Answer 39

SELECT col_name CONCAT(col_name1,’, ‘,col_name2,’, ‘,col_name,)AS alias_name FROM table_name;

SELECT CustomerName, CONCAT(Address,’, ‘,City,’, ‘,PostalCode,’, ‘,Country) AS Address
FROM Customers;

Question 40

HOW TO CREATE A TABLE SIMILAR TO AN EXISITN TABLE?

Answer 40

CREATE TABLE table2 SIMILAR TO table1;

Question 41

HOW TO INSERT VALUES FROM ONE TABLE INTO AN EXISTING TABLE WITHOUT AFFECTING THE ROWS OF THE EXISTING TABLE?

Answer 41

INSERT INTO table2 SELECT * FROM table1,

Question 42

unqiue constraint create

Answer 42

CREATE TABLE table_name( col_name1 datatype not null/null, col_name2 datatype,….,UNIQUE(col_name));

Question 43

TO ADD UNQIUE CONSTRAINT ON A COLUMN

Answer 43

ALTER TABLE table_name ADD unique (col_name);

Question 44

tO DROP UNQIUE CONSTRAINT ON A COLUMN

Answer 44

ALTER TABLE table_name DROP INDEX UNQIUE