In this post, we are going to make a full database in MySQL for class 12, with all solved queries, If you want to add a new query please comment on this post we will add your query in this post.
1. Creating Database in MySQL
CREATE DATABASE CUSTOMER;2. Using Database CUSTOMER
USE COSTUMER;Creating Table in COSTUMER
CREATE TABLE CUSTOMERS(
ID INT,
NAME VARCHAR(255),
AGE INT,
ADDRESS CHAR(25),
AMOUNT DECIMAL(18, 2),
PRIMARY KEY(ID)
);You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise, you can use the DESC command as follows −
DESC CUSTOMERS;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| NAME | varchar(255) | YES | | NULL | |
| AGE | int | YES | | NULL | |
| ADDRESS | char(25) | YES | | NULL | |
| AMOUNT | decimal(18,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
5 rows in set (0.13 sec)Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.
The SQL INSERT INTO Statement is used to add new rows of data to a table in the database. INSERT INTO CUSTOMERS
-> VALUES (1, 'Anmol Garg', 19, 'New Delhi', 1310.65);
Query OK, 1 row affected (0.30 sec)Adding more data to the Database
INSERT INTO CUSTOMERS VALUES (2, 'Ram', 25, 'Mumbai', 2758.84);
INSERT INTO CUSTOMERS VALUES (3, 'Shyam', 46, 'Banglore', 8547.68);
INSERT INTO CUSTOMERS VALUES (4, 'Ganshyam', 48, 'Tamil Nadu', 39084.48);
Query OK, 1 row affected (0.15 sec)+----+------------+------+------------+----------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+------------+----------+
| 1 | Anmol Garg | 19 | New Delhi | 1310.65 |
| 2 | Ram | 25 | Mumbai | 2758.84 |
| 3 | Shyam | 46 | Banglore | 8547.68 |
| 4 | Ganshyam | 48 | Tamil Nadu | 39084.48 |
+----+------------+------+------------+----------+
4 rows in set (0.07 sec)The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets
SELECT * FROM CUSTOMERS;
+----+------------+------+------------+----------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+------------+----------+
| 1 | Anmol Garg | 19 | New Delhi | 1310.65 |
| 2 | Ram | 25 | Mumbai | 2758.84 |
| 3 | Shyam | 46 | Banglore | 8547.68 |
| 4 | Ganshyam | 48 | Tamil Nadu | 39084.48 |
+----+------------+------+------------+----------+
4 rows in set (0.07 sec)The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records. The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we would examine in the subsequent chapters.
The following code is an example which would fetch the ID, Name and Amount fields from the CUSTOMERS table, where the salary is greater than 2000 −
SELECT ID, NAME, AMOUNT FROM CUSTOMERS WHERE AMOUNT > 2000;
+----+----------+----------+
| ID | NAME | AMOUNT |
+----+----------+----------+
| 2 | Ram | 2758.84 |
| 3 | Shyam | 8547.68 |
| 4 | Ganshyam | 39084.48 |
+----+----------+----------+
3 rows in set (0.07 sec)The following query is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table for a customer with the name Ram. Here, it is important to note that all the strings should be given inside single quotes (''). Whereas, numeric values should be given without any quote as in the above example
SELECT ID, NAME, AMOUNT FROM CUSTOMERS WHERE NAME = "Ram";
+----+------+---------+
| ID | NAME | AMOUNT |
+----+------+---------+
| 2 | Ram | 2758.84 |
+----+------+---------+
1 row in set (0.03 sec)The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement.
The AND Operator The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
SELECT ID, NAME, AMOUNT FROM CUSTOMERS WHERE AMOUNT < 2000 AND AGE < 25;
+----+------------+---------+
| ID | NAME | AMOUNT |
+----+------------+---------+
| 1 | Anmol Garg | 1310.65 |
+----+------------+---------+
1 row in set (0.00 sec)The OR Operator The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
SELECT ID, NAME, AMOUNT FROM CUSTOMERS WHERE AMOUNT < 2000 OR AGE < 25;
+----+------------+---------+
| ID | NAME | AMOUNT |
+----+------------+---------+
| 1 | Anmol Garg | 1310.65 |
+----+------------+---------+
1 row in set (0.00 sec)The SQL UPDATE Query is used to modify the existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.
The following query will update the ADDRESS for a customer whose ID number is 6 in the table.
UPDATE CUSTOMERS SET ADDRESS = 'Srinagar' WHERE ID = 2;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0 Output
SELECT * FROM CUSTOMERS;
+----+------------+------+------------+----------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+------------+----------+
| 1 | Anmol Garg | 19 | New Delhi | 1310.65 |
| 2 | Ram | 25 | Srinagar | 2758.84 |
| 3 | Shyam | 46 | Banglore | 8547.68 |
| 4 | Ganshyam | 48 | Tamil Nadu | 39084.48 |
+----+------------+------+------------+----------+
4 rows in set (0.00 sec)If you want to modify all the ADDRESS and the AMOUNT column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough as shown in the following code block.
UPDATE CUSTOMERS SET ADDRESS = 'New Delhi', AMOUNT = 9999.00;
Query OK, 4 rows affected (0.10 sec)
Rows matched: 4 Changed: 4 Warnings: 0OUTPUT
SELECT * FROM CUSTOMERS;
+----+------------+------+-----------+---------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+-----------+---------+
| 1 | Anmol Garg | 19 | New Delhi | 9999.00 |
| 2 | Ram | 25 | New Delhi | 9999.00 |
| 3 | Shyam | 46 | New Delhi | 9999.00 |
| 4 | Ganshyam | 48 | New Delhi | 9999.00 |
+----+------------+------+-----------+---------+
4 rows in set (0.07 sec)The SQL DELETE Query is used to delete the existing records from a table. You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise, all the records would be deleted.
The following code has a query, which will DELETE a customer, whose ID is 3
DELETE FROM CUSTOMERS WHERE ID = 3;
Query OK, 1 row affected (0.19 sec)Output
SELECT * FROM CUSTOMERS;
+----+------------+------+-----------+---------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+-----------+---------+
| 1 | Anmol Garg | 19 | New Delhi | 9999.00 |
| 2 | Ram | 25 | New Delhi | 9999.00 |
| 4 | Ganshyam | 48 | New Delhi | 9999.00 |
+----+------------+------+-----------+---------+
3 rows in set (0.03 sec)The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator. The percent sign (%) The underscore (_) The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. These symbols can be used in combinations.
Following is an example, which would display all the records from the CUSTOMERS table, where the AMOUNT starts with 200.
SELECT * FROM CUSTOMERS
-> WHERE AMOUNT LIKE '999%';
+----+------------+------+-----------+---------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+-----------+---------+
| 1 | Anmol Garg | 19 | New Delhi | 9999.00 |
| 2 | Ram | 25 | New Delhi | 9999.00 |
| 4 | Ganshyam | 48 | New Delhi | 9999.00 |
+----+------------+------+-----------+---------+
3 rows in set (0.00 sec)The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in ascending order by default.
The following code block as an example, which would sort the result in ascending order by the NAME and the AMOUNT −
SELECT * FROM CUSTOMERS ORDER BY NAME, AMOUNT;
+----+------------+------+-----------+---------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+-----------+---------+
| 1 | Anmol Garg | 19 | New Delhi | 9999.00 |
| 4 | Ganshyam | 48 | New Delhi | 9999.00 |
| 2 | Ram | 25 | New Delhi | 9999.00 |
+----+------------+------+-----------+---------+
3 rows in set (0.00 sec)The following code block as an example, which would sort the result in descending order by NAME
SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
+----+------------+------+-----------+---------+
| ID | NAME | AGE | ADDRESS | AMOUNT |
+----+------------+------+-----------+---------+
| 2 | Ram | 25 | New Delhi | 9999.00 |
| 4 | Ganshyam | 48 | New Delhi | 9999.00 |
| 1 | Anmol Garg | 19 | New Delhi | 9999.00 |
+----+------------+------+-----------+---------+
3 rows in set (0.00 sec)The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
If you want to know the total amount of the salary of each customer, then the GROUP BY query would be as follows.
SELECT NAME, SUM(AMOUNT) FROM CUSTOMERS GROUP BY NAME;
+------------+-------------+
| NAME | SUM(AMOUNT) |
+------------+-------------+
| Anmol Garg | 9999.00 |
| Ram | 9999.00 |
| Ganshyam | 9999.00 |
+------------+-------------+
3 rows in set (0.03 sec)HOPE YOU LEARN MORE ABOUT MYSQL FROM THIS