Create a MySQL database and tables

Question- How will You create a MySQL database and tables ?
Answer- If you want to create a database and set up tables for the same use the following two sql commands:

 CREATE DATABASE - create the database
 CREATE TABLE - create the table
 INSERT - To add/insert data to table

• CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database

• CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.

• INSERT inserts new rows into an existing table.

Procedure for creating a database and a 

sample table

Login as the mysql root user to create database:
$ mysql -u root -p
Output:

mysql>

Add a database called books
mysql> CREATE DATABASE books;

Now database is created. Use a database with use command:
mysql> USE books;

Now create a table called authors with name, email and id:
mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));

Display your table name just created:
mysql> SHOW TABLES;

Output:

+-----------------+
| Tables_in_books |
+-----------------+
| authors         |
+-----------------+
1 row in set (0.00 sec)

Now add a data / row to table books using INSERT statement:
mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Output:

Query OK, 1 row affected (0.00 sec)

Add few more rows:
mysql> INSERT INTO authors (id,name,email) VALUES(2,"Priya","p@gmail.com");
INSERT INTO authors (id,name,email) VALUES(3,"Tom","tom@yahoo.com");

Now display all rows:
mysql> SELECT * FROM authors;

Output:

+------+-------+---------------+
| id   | name  | email         |
+------+-------+---------------+
|    1 | Vivek | xuz@abc.com   |
|    2 | Priya | p@gmail.com   |
|    3 | Tom   | tom@yahoo.com |
+------+-------+---------------+
3 rows in set (0.00 sec)

For additional columns in existing table- how will you add mysql column in exixting table

extend a MySQL table with additional columns

Question- How will You extend a MySQL table with additional columns ?
Answer- 

MySQL tables are easy to extend with additional columns.

To add a column called email to the contacts table created in Create a basic MySQL table with a datatype of VARCHAR(80)

use the following SQL statement:

ALTER TABLE contacts ADD email VARCHAR(60);

This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

If you want the new column to be first, use this statement:

ALTER TABLE contacts ADD email VARCHAR(60) FIRST;

Note About SQL Functions

SQL Functions

SQL functions are built into Oracle and are available for use in various appropriate
SQL statements. Do not confuse SQL functions with user functions written in
PL/SQL.

If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle implicitly converts the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT, NVL, and REPLACE.
In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter "function" appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.

Note: When you apply SQL functions to LOB columns, Oracle
creates temporary LOBs during SQL and PL/SQL processing. You
should ensure that temporary tablespace quota is sufficient for
storing these temporary LOBs for your application.