SQL CRUD

Question 1

How to create and use a database?

Answer 1

CREATE DATABASE database_name;

USE database_name;

Question 2

How to create a table with 2 text fields?

Answer 2

CREATE TABLE table_sample ( name VARCHAR(10), type_sample VARCHAR(6) );

Question 3

What data type sql have? ( 8 types )

Answer 3

VARCHAR(10) -> string
CHAR(1) -> character with fixed size
BLOB -> big text

INT -> integer number
DEC -> decimal number

DATE -> date
DATETIME -> date and time
TIME -> Time

Question 4

How to show a table structure?

Answer 4

use DESC -> decribe

DESC table_name;

Question 5

How to remove table?

Answer 5

DROP TABLE table_name;

Question 6

How to add new value to the table?

Answer 6

INSERT INTO my_contacts

(last_name, first_name) VALUES (‘Андерсон’, ‘Джиллиан’);

Question 7

How to create a table and don’t allow null value?

Answer 7

CREATE TABLE table_sample ( name VARCHAR(10) NOT NULL );

Question 8

How to create a table with a default value?

Answer 8

CREATE TABLE table_sample ( name VARCHAR(10) default ‘aaa’ );

Question 9

How to delete the rows from the table?

Answer 9

DELETE FROM Customers WHERE City = ‘Paris’;

Question 10

How to update the row?

Answer 10

# with 1 field
UPDATE Customers SET city = 'Berlin' WHERE city = 'London';
# with 2 fields for updation 
UPDATE Customers SET city='London', country='Germany' WHERE city = 'Berlin';

Question 11

How to update the integer row using already existing values?

Answer 11

# price is already existing value 
UPDATE Products SET price = price + 1 WHERE categoryID = 1;

Question 12

How to create a primary key for the table?

Answer 12

CREATE TABLE sample (sample_id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (sample_id));
# OR
CREATE TABLE sample (sample_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

Question 13

How to add a new column?

Answer 13

ALTER TABLE sample ADD COLUMN name VARCHAR(10);

Question 14

How to rename table?

Answer 14

ALTER TABLE sample RENAME TO samples;

Question 15

How to rename column and change column type?

Answer 15

ALTER TABLE samples CHANGE COLUMN number number1 VARCHAR(10) DEFAULT ‘ddd’;

Question 16

How to update only column type?

Answer 16

ALTER TABLE samples MODIFY COLUMN number1 INT ;

Question 17

How to remove column?

Answer 17

ALTER TABLE samples DROP COLUMN name1;

Question 18

How to update the column name with case statement?

Answer 18

UPDATE Products SET
	ProductName = 
    CASE 
    	WHEN Price = 20 THEN 'Hello 20'
        WHEN Price = 30 THEN 'Hello 30'
        ELSE 'Nothing'
    END;

Question 19

How to insert values from another table to your table?

Answer 19

INSERT INTO my_table (name) SELECT name FROM another_table;

Question 20

How to create table with preinstalled values from another table?

Answer 20

CREATE TABLE profession (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)) AS SELECT name FROM samples;

Question 21

How to save request and reuse it later?

Answer 21

# Create view
CREATE VIEW my_sample_view AS SELECT * FROM Customers;

# then you can use this request
SELECT * FROM my_sampe_view;

Question 22

How to drop view?

Answer 22

DROP VIEW my_sample_view;