1. String Manupulation

   1. CONCAT: function allows you to combine text :
   2. SELECT CONCAT(firstname, ' ', lastname) AS name
   3. LOWER(firstname)
   4. SUBSTRING(firstname, 1, 3) 1st is starting position 2nd is Number of charcters (us character ko bhi include karna hai)
   5. REPLACE(city, 'New York', 'NY')

   SELECT CONCAT(
       SUBSTRING(firstname, 1, 1), 
       '. ', 
       UPPER(lastname)) AS name
   FROM Customers 
   

2. Case usko end karna hai

   SELECT firstname, lastname,  
   CASE
     WHEN age >= 65 THEN 'Senior'
     WHEN age >= 25 AND age < 65 THEN 'Adult'
     ELSE 'Youth'
   END AS category
   FROM Customers 
   

3. AUTO_INCREMENT

   CREATE TABLE Customers (
     id int NOT NULL AUTO_INCREMENT,
     firstname varchar(255),
     lastname varchar(255)
   );
   ----------------------------------------------------
   INSERT INTO Customers (firstname, lastname, city, age)
   VALUES 
   ('demo', 'demo', 'Paris', 52),
   ('test', 'test', 'London', 21);  
   
   SELECT * FROM Customers;
   ------------------------------------------
   ALTER TABLE Customers
   AUTO_INCREMENT=555
   

4. KEYS: They are used to define relationships between tables.

   1. The primary key constraint is used to uniquely identify rows of a table.

      CREATE TABLE Customers (
        id int NOT NULL AUTO_INCREMENT,
      firstname varchar(255),
      lastname varchar(255),
        PRIMARY KEY (id)
      );
      

   2. A Foreign Key is a column in one table that refers to the Primary Key in another table.

      CREATE TABLE PhoneNumbers (
        id int NOT NULL AUTO_INCREMENT,
        customer_id int NOT NULL,
        number varchar(55),
      type varchar(55),
        PRIMARY KEY (id),
        FOREIGN KEY (customer_id) REFERENCES Customers(id)
      );
      

   3. Unique NULL values are ignored by UNIQUE, meaning you can have multiple NULL values in a UNIQUE column.

      ALTER TABLE Customers
      ADD UNIQUE (lastname)
      

5. Multiple Table

   SELECT firstname, lastname, city, number, type 
   FROM Customers, PhoneNumbers
   ***WHERE Customers.id = PhoneNumbers.customer_id*** 
   

   SELECT Users.id
   From Users
   

6. Joins

   SELECT firstname, lastname, city, number, type
   FROM Customers JOIN PhoneNumbers ON Customers.id = PhoneNumbers.customer_id
   

   

   ----alias: short form
   SELECT C.firstname, C.lastname, C.city, PN.number, PN.type
   FROM Customers AS C JOIN PhoneNumbers AS PN
   ON C.id = PN.customer_id 
   
   ------left join
   SELECT C.firstname, C.lastname, C.city, PN.number, PN.type
   FROM Customers AS C LEFT JOIN PhoneNumbers AS PN
   ON C.id = PN.customer_id
   
   C ko left join karo pn se, means pn will be left side
   

7. Union:

   The UNION operator is used to combine the result-sets of two or more SELECT statements.

   UNION ALL is similar to UNION, but does not remove the duplicates:

   SELECT firstname, lastname, age FROM Customers
   UNION
   SELECT firstname, lastname, age FROM Contacts
   
   -----------------
   SELECT firstname, lastname, age FROM Customers
   WHERE age > 30
   UNION
   SELECT firstname, lastname, age FROM Contacts
   WHERE age < 25
   

8. AVerage

SELECT AVG(count) FROM 
(SELECT C.id, COUNT(PN.number) AS count
FROM Customers AS C LEFT JOIN PhoneNumbers AS PN
ON C.id = PN.customer_id
GROUP BY C.id) AS Numbers

https://www.sololearn.com/certificates/CC-E9GCCPZR