MySQL

Anmerkungen:

• https://www.youtube.com/watch?v=7fOdo8PhPaw&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy&index=7

1. Numeric type
   1. BigInt (Really Larger Numebrs)
   2. Int (Big Values)
   3. Small Int (-32,768 to 32, 767)
   4. Tinyint (0 to 255)
   5. Decimal (m,d)
   6. Numeric (same As Decimal)
   7. Float (m,d)
2. Date and Time Data Type
   1. Datetime (YYYY-MM-DD HH:MM:SS)
   2. Date

      Anmerkungen:

      • MySQL - (YYYY-MM-DD) Oracle - (DD-MMM-RR)
      1. Question

         Anmerkungen:

         • How to See the Format in results ?
   3. Time (HH:MM:SS)
   4. Year (YYYY)
3. Character Type
   1. Char (M)
   2. Varchar (M)
   3. Text (to store large amount of text)
4. Blob (binary large object) - for storing images
5. Questions

   Anmerkungen:

   • Unable to change the Column data type from Numeric to INT in SQL Deveopler tool... Why? Can we change the data type after table is created via SQL commands ??

Anmerkungen:

• https://www.youtube.com/watch?v=bIFB-rz315U&index=8&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy

1. Not Null Constraint

   Anmerkungen:

   • If you want to make sure taht a field gets some value for every row in the table, apply the not null constraint to it.
2. Default Constraint

   Anmerkungen:

   • If while populating a table you miss giving a colum some value, the default value will be used
3. Unique constraint

   Anmerkungen:

   • Unique constraint makes sure that all values entered for the field on which the constraint applied are different
4. Primary Key Constraint

   Anmerkungen:

   • Not Null + Unique

1. Create Table

   Anmerkungen:

   • Name the Table Define the Columns Mention data types of columns
   1. Syntax

      Anmerkungen:

      • CREATE TABLE table_name ( col1 datatype, col2 datatype, . . col n datatype, PRIMARY KEY (column));
   2. Example

      Anmerkungen:

      • CREATE TABLE employee ( id int PRIMARy KEY,  name varchar (50),  dob date,  email VARCHAR(50) ) =============== Create table emp_info as select id, name from employee;
   3. Questions

      Anmerkungen:

      • 1. How to check the Constraints of a table ? 2. How to see the list of tables in Oracle ? (SHOW Tables - in MySQL) 3. How to check the two tables are having same columns or not ? 4. DB defined column names - like - ROWID in Oracle DB ?
2. Remove Table

   Anmerkungen:

   • Deleting a table from database. Make sure to ensure the table by using  1) SHOW TABLES 2) DESC table_name
   1. Syntax

      Anmerkungen:

      • DELETE TABLE table_name;
   2. Questions

      Anmerkungen:

      • How to Remove only data from table ? any way to restore the deleted table ?
3. TRUNCATE
   1. Syntax

      Anmerkungen:

      • TRUNCATE TABLE tab_name

1. INSERT

   Anmerkungen:

   • https://www.youtube.com/watch?v=Tet3Z7Yb2gg&list=PL_RGaFnxSHWr_6xTfF2FrIw-NAOo3iWMy&index=12
   1. Syntax

      Anmerkungen:

      • INSERT INTO table_name (Col 1, col3) VALUES (val 1, val 3) ==================== INSERT INTO table_name VALUES (val 1, val 2, .., val n) >> order of the values need to be correct in second one ==================== INSERT INTO emp_info (id,name, dob, email)  select from id, name, dob, email from employee. >> we can insert the data from other table also.
   2. Questions

      Anmerkungen:

      • Can we avoid the column names, when inserting from another table like -  INSERT INTO emp_info (id,name,dob, email) SELECT all FROM employee;
2. SELECT
   1. Syntax

      Anmerkungen:

      • SELECT col1, col3,.., coln FROM Table_name
   2. WHERE Clause

      Anmerkungen:

      • Used to mention the conditions & joining the differnt tables
      1. Syntax

         Anmerkungen:

         • SELECT col1 FROM table_name [WHERE condition]
   3. TOP / LIMIT Clause

      Anmerkungen:

      • Used to fect the top N number of records from query. Not all DBs support Oracle supports ROWNUM while MySQL uses LIMIT
      1. Syntax

         Anmerkungen:

         • MySQL -  SELECT Col-ListFROM table_name[WHERE condition]LIMIT n;
   4. ORDER BY Clause
      1. Syntax

         Anmerkungen:

         • SELECT col-list FROM table_name [WHERE Condition] [ORDER BY Col2,Col4] [ASC/DSC]
   5. GROUP BY Clause

      Anmerkungen:

      • GROUP BY always follow the WHERE Clause and must precede the ORDER BY clause
      1. Syntax

         Anmerkungen:

         • SELECT col-list FROM table_name Where Condition [GROUP BY col] [ORDER BY col]
   6. DISTINT keyword

      Anmerkungen:

      • Used to fetch the single record only, when table is having the same value/data in multiple rows
   7. AS Clause

      Anmerkungen:

      • Giving different name to column headers in SELECT
3. UPDATE
   1. Syntax

      Anmerkungen:

      • UPDATE table_name SET col2=val2,..coln=valn [WHERE condition]
4. DELETE
   1. Syntax

      Anmerkungen:

      • DELETE FROM table_name [WHERE condition]
5. JOINS
   1. Syntax

      Anmerkungen:

      • SELECT e.ename, d.dname From emp e join DEPT d on e.deptno=d.deptno;
   2. Questions

      Anmerkungen:

      • Can we use JOINS for update / delete ?
   3. LEFT JOIN
   4. RIGHT JOIN
   5. FULL OUTER JOIN
6. VIEW

   Anmerkungen:

   • View is same as a table without any storage.
   1. Syntax

      Anmerkungen:

      • CREATE VIEW EMP_VIEW AS SELECT EMPNO,ENAME FROM EMP
7. SubQueries

Anmerkungen:

• These are used with WHERE clause to limit the number of rows in output

1. Arithmetic
   1. Addition (+)
   2. Subtraction (-)
   3. Multiplication (*)
   4. Division (/)
   5. Modular Division (%)
   6. Questions

      Anmerkungen:

      • SELECT 5 + 5; is not working in SQL Developers. May be we can't use SQL Developer as calculator ??
2. Comparison

   Anmerkungen:

   • To compare data present in columns to data specified in conditions
   1. Equal (=)
   2. Not Equal (<>)
   3. Greater Than (>)
   4. Less Than (<)
   5. Greather Than or Equal To(>=)
   6. Lessthan Or Equal to (<=)
3. Logical
   1. ALL
   2. AND
   3. BETWEEN
   4. IN
   5. LIKE

      Anmerkungen:

      • Used to compare a value to similar values in a table in database Used with Wildcard characters - % (Percentage) &amp; _ (underscore) % - substitution for zero, one or many char's _ - stands for one character or number
      1. Syntax

         Anmerkungen:

         • SELECT Column-list  FROM table_name [WHERE col LIKE '_XXX%']
   6. OR
   7. IS NULL

Anmerkungen:

• Map is for MySQL. =========http://www.oracle-dba-online.com/sql/oracle_sql_functions.htm

1. ABS
2. ROUND
3. DIV()
4. MOD()
5. CEIL
6. FLOOR
7. EXP(x)

   Anmerkungen:

   • e = 2.57
8. LOG(X)
9. POWER(X,Y)
10. GREATEST(X,Y,Z)
11. LEAST
12. RADIANS

    Anmerkungen:

    • Not working in ORACLE SQL
13. SQRT
14. TRUNCATE
15. RAND
16. CONCAT
17. UPPER
18. LOWER
19. INITCAP
20. TRIM
21. SUBSTR

    Anmerkungen:

    • select substr('mohammed sami',10,3) from dual; Substr -------- sam
22. RIGHT
23. LEFT
24. LENGTH
25. INSERT
26. REPEAT
27. REPLACE
28. REVERSE
29. STRCMP
30. DATE & TIME
    1. ADDDATE
    2. SUBDATE
    3. DAYNAME
    4. CURDATE & CURTIME
    5. NOW()
    6. MAKEDATE
    7. MONTHNAME
    8. TIMEDIFF
    9. TIME_TO_SEC
31. Aggregate
    1. COUNT
    2. MAX
    3. MIN
    4. SUM
    5. AVERAGE