ASSIGNMENT # 4 - abeerafatima/learning-database GitHub Wiki

what is DDL?

Data Definition Language (DDL) statements are used to define the database structure or schema. Data Definition Language (DDL) is a vocabulary used to define data structures in SQL Server . Use these statements to create, alter, or drop data structures in an instance of SQL Server.

CREATE - to create objects in the database.

ALTER - alters the structure of the database.

DROP _- delete objects from the database

TRUNCATE _- remove all records from a table, including all spaces allocated for the records are removed

COMMENT - _add comments to the data dictionary

RENAME -_ rename an object

what is DML?

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT - retrieve data from the a database

**INSERT **- insert data into a table

UPDATE - updates existing data within a table

DELETE _- deletes all records from a table, the space for the records remain

MERGE_ - UPSERT operation (insert or update)

CALL -_ call a PL/SQL or Java subprogram

EXPLAIN PLAN_ - explain access path to data

LOCK TABLE_ - control concurrency

DIFFERENCE BETWEEN DDL $ DML:

Manipulation Language (also known as DML) is a family of computer languages. They are used by computer programs, and/or database users, to manipulate data in a database – that is, insert, delete and update this data in the databaSE.This is a language used for databases, and is designed specifically for managing data in relational database management systems (or RDBMS).

Data Definition Language (also known as DDL) is a computer language used to define dDDL was used within the schema of the database in order to describe the records, fields, and ‘sets’ that made up the user Data Model. It was at first a way in which programmers defined SQL.The DDL is used mainly to create – that is to make a new database, table, index or stored query. A CREATE statement in SQL literally creates an object inside any RDBMS.

SQL DATATYPES :

In MySQL there are three main types : text, number, and Date/Time types.

TEXT TYPES:

Data type Description

CHAR(size)

VARCHAR(size)

TINYTYPE

TEXT

BLOB

MEDIUMTEXT

MEDIUMBLOB

LONGTEXT

LONGBLOB

ENUM(x,y,z,etc.)

Number types:

Data type Description

TINYINT(size) -128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis

SMALLINT(size) -32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis.

MEDIUMINT(size) -8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis

INT(size) -2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis

BIGINT(size) -9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis

FLOAT(size,d) A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

DOUBLE(size,d) A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

DECIMAL(size,d) A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

DATE TYPES

Data type Description

DATE() A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12-31'

DATETIME() *A date and time combination. Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'

TIMESTAMP() *A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MI:SS Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC

TIME() A time. Format: HH:MI:SS Note: The supported range is from '-838:59:59' to '838:59:59'

YEAR() A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

VARCHAR() VS CHAR:

CHAR Data Type is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.

On the other hand VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.