SQL Data types and Constarints.pptx

SQL Data types and Constarints

1. S TRUCTURED Q UERY L ANGUAGE DATABASE MANAGEMENT SYSTEM

2. SQL - SYNTAX SQL keywords are NOT case sensitive: select is the same as SELECT Requires a semicolon at the end of each SQL statement.

3. SQL - SYNTAX For more information on SQL syntax visit the link https://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html

4. SQL - DATABASE A database most often contains one or more tables. Each table is identified by a name (e.g. "Customers" or "Orders") Tables contain records (rows) with data.

5. SQL – DATABASE -TABLE CUSTOMER Record 5 records Attributes Primary Key

6. SQL – DATABASE -TABLE SQL Command Description CREATE DATABASE   Creates a new database CREATE TABLE   Creates a new table ALTER TABLE   Modifies a table DROP TABLE   Deletes a table ALTER DATABASE Modifies a database SELECT Extracts data from a database UPDATE   Updates data in a database DELETE Deletes data from a database INSERT INTO Inserts new data into a database

7. SQL – DATATYPES

8. DATA TYPE Description INT(size) used for the integer value. INTEGER(size) It is equal to INT(size). FLOAT(size, d) It is used to specify a floating point number. size - Total no of digits. d- no of digits after decimal point. BOOL It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true. SQL – DATATYPES - INTEGER

9. SQL – DATATYPES - STRING DATA TYPE Description char(n) It is a fixed width character string data type. varchar(n) It is a variable width character string data type. Text / TEXT(N) It holds a string . BOOL It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values are considered as true.

10. SQL – DATATYPES - Date and Time DATA TYPE Description DATE It is used to specify date format YYYY-MM-DD. DATETIME(fsp) It is used to specify date and time combination. Its format is YYYY-MM-DD hh:mm:ss . TIME(fsp) It is used to specify the time format. Its format is hh:mm:ss .

11. SQL DATATYPES- MORE Visit this link for more datatypes in SQL https://www.javatpoint.com/sql-data-types

12. SQL OPERATORS Three types of operators in SQL: SQL Arithmetic Operators SQL Comparison Operators SQL Logical Operators

13. SQL OPERATORS - ARITHMETIC

14. SQL OPERATORS - COMPARISON

15. SQL OPERATORS - LOGICAL

16. SQL keys SQL Keys are Candidate key Primary Key Foreign Key Alternate Key Composite Key

17. SQL Primary key SQL PRIMARY KEY A column or columns is called  primary key (PK)  that  uniquely identifies each row in the table . A primary key cannot have null value . There can be no duplicate value for a primary key. A table can contain only one primary key constraint.

18. SQL FOREIGN key SQL FOREIGN KEY In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables. A foreign key can have null value . There can be duplicate value for a foreign key. A table can contain many one foreign key constraint.

19. SQL UNIQUE KEY Uniquely determines a row which isn’t primary key. A Unique key can have null value . There can be more than one unique key Primary Key

20. SQL Constraints SQL constraints are used to specify rules for the data in a table. NOT NULL   - Ensures that a column cannot have a NULL value UNIQUE  - Ensures that all values in a column are different PRIMARY KEY   - A combination of a NOT NULL and UNIQUE. Uniquely Identifies each row in a table FOREIGN KEY  - Uniquely identifies a row/record in another table CHECK  - Ensures that all values in a column satisfies a specific condition DEFAULT  - Sets a default value for a column when no value is specified INDEX  - Used to create and retrieve data from the database quickly

21. SQL Constraints – NOT NULL NOT NULL constraint enforces a column to NOT accept NULL values . By default, a column can hold NULL values.

22. SQL Constraints – UNIQUE The UNIQUE constraint ensures that all values in a column are different. A PRIMARY KEY constraint automatically has a UNIQUE constraint.  A Table can have many UNIQUE constraints

23. SQL Constraints – CHECK The CHECK constraint is used to limit the value range that can be placed in a column. A CHECK constraint on a single column allows only certain values for that column.

24. SQL Constraints – DEFAULT The DEFAULT constraint is used to provide a default value for a column. The default value will be added to all new records IF no other value is specified.

25. SQL Constraints – AUTO INCREMENT Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.