# SQL

- **Importing and exporting mysql dump file**
    1. To import:
    
    ```bash
    mysql -u username -p database_name < file.sql
    ```
    
    1. To export:
    `mysqldump -u root -p hospitals > C:\Users\harsh\Downloads\Documents\hospitals.sql`
	2. to export only schema
	  
- Postgresql
    
    to load postgresql database
    
    ```bash
    psql -U postgres smart-brain
    ```
    
    Here postgres is owner name and smart-brain is name of data base. It will prompt for password which is U2FsdGVkX1/k+uOTLs4gSYVmxYAJEQybNVGnxIQVHyg=. Refer to 
    
    [Web development](../../Export-9fad9947-5bd5-4195-952b-6512c5a4beb1/Web%20development%2083dd4e6675f04ba782a6865a434a0598/Web%20development%2083dd4e6675f04ba782a6865a434a0598.md)
    
    for decrypting it.
    
    To load postgresql without database:
    
    `psql -U postgres`
    
    To create database:
    
    `createdb mydb -U postgres`
    
    or
    
    ```bash
    psql -U postgres
    create database mydb;
    ```
    
    To get list of database:
    
    `\l`
    
    To connect to database when login as user
    
    `\connect dbname`
    
    `\q`: To quit
	- List tables, views and sequences
	  `\d`
	- Describe tables
	  `\d <table_name>`
    
- MYSQL
    
    Online compiler: [https://www.mycompiler.io/new/sql](https://www.mycompiler.io/new/sql)
    
    To load mysql shell in command prompt:
    
    `mysql -u root -p`
    
    Here root is owner_name. This will prompt for password which is ‘password’.
    
    To get list of database:
    
    `show databases;`
	
	To create database 
	```sql
	 drop database if exists database_name; 
	 create database database_name; 
	 ```
    
    To load database
    
    `use hospitals;`
    
    hospitals here is name of database
    
    Once database is loaded, to get list of tables:
    
    `show tables;`
    
    To get schema of tables:
    
    `desc patients;` or `describe patients;`
    
    where patients is name of table
    
    - Creating table
        
        ```sql
        create table project (
        	emp_id int not null,
            id int not null,
            name varchar(30),
            location varchar(40),
            primary key(emp_id)
        );
        ```
     -  Adding Auto increment
     
		```sql
		-- To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
		ALTER TABLE Persons AUTO_INCREMENT=100; 
		
		alter table employee 
		modify emp_id int not null auto_increment;
		```  

    - Constraints
        
        [How to use ADD CONSTRAINT in SQL (with examples) (cockroachlabs.com)](https://www.cockroachlabs.com/blog/sql-add-constraint/)
        
    - LIKE
        
        [SQL LIKE Operator (w3schools.com)](https://www.w3schools.com/SQL/sql_like.asp)
        
        SELECT *column1, column2, ...* FROM *table_name* WHERE *columnN* LIKE *pattern*;
        
        **Tip:** You can also combine any number of conditions using `AND` or `OR` operators.
        
        Here are some examples showing different `LIKE` operators with '%' and '_' wildcards:
        
        | LIKE Operator | Description |
        | --- | --- |
        | WHERE CustomerName LIKE 'a%' | Finds any values that start with "a" |
        | WHERE CustomerName LIKE '%a' | Finds any values that end with "a" |
        | WHERE CustomerName LIKE '%or%' | Finds any values that have "or" in any position |
        | WHERE CustomerName LIKE '_r%' | Finds any values that have "r" in the second position |
        | WHERE CustomerName LIKE 'a_%' | Finds any values that start with "a" and are at least 2 characters in length |
        | WHERE CustomerName LIKE 'a__%' | Finds any values that start with "a" and are at least 3 characters in length |
        | WHERE ContactName LIKE 'a%o' | Finds any values that start with "a" and ends with "o" |
    - JOIN
        
        ```sql
        create table Member (
        	'Member ID' int not null,
            'First Name' varchar(50),
            'Last Name' varchar(50),
            gender varchar(10),
            age int not null,
            country varchar(50),
            joined_date date,
            primary key('Member ID')
        );
        
        create table Booking (
        	BookingID int not null,
            Room varchar(50),
            
            Days int, 
            cost per day float,
            status varchar(30),
            primary key(BookingID)
        );
        
        create table Make (
        	Date date,
            MemberID int not null,
            BookingID int not null
        );
        
        -- Main  ............
        SELECT 'First Name', 'Last Name', Age, 'Date'
        FROM Member as me JOIN Make as ma JOIN Booking as b
        ON me.'Member ID'=ma.'MemberID' and ma.BookingID=b.BookingID;
        ```
        
    - Insert into
        
        ```sql
        INSERT INTO table_name (column1, column2, column3, ...)
        VALUES (value1, value2, value3, ...);
        ```
        
        or 
        
        ```sql
        INSERT INTO table_name
        VALUES (value1, value2, value3, ...);
        ```
        
        For multiple rows
        
        ```sql
        INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
        VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ), (5, 'Hardik', 27, 'Bhopal', 8500.00 ), (6, 'Komal', 22, 'MP', 4500.00 );
        ```
        
- Delete records
  ```sql
  -- to delete all records
  delete from table_name;
  
  -- to delete record id=5
  delete from table_name 
  where id=5;
  
  ```
    - Date
        
        ```sql
        create table Member (
        	... 
        .....
        joined_date date,
          
        );
        
        insert into Member
        values(1582,'Mara','Hash','Female',25,'Uganda','2020-08-16'), (3549,'Kathleen','Hanner','Female',25,'United States','2017-12-12');
        
        ```
        
    
    If there column name contain spaces then enclose them with ticks → `MEMBER ID` 
    
- Handwritten
    
    [sql.pdf](../../_resources/sql.pdf)
    
    [sql.pdf](../../_resources/sql%201.pdf)