SQL Constraints (Part -5)

 SQL constraints are conditions / rules that apply on the data columns of a table which is used to limit the type of data that goes inside the table.

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command.

There are 3 types of Constraints:

A)     Key Constraint

B)     Domain Constraint

C)     Referential Integrity Constraint

The PRIMARY KEY constraint uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.

A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely. Unlike primary key, there can be multiple unique constraints defined per table. The code syntax for UNIQUE is quite similar to that of PRIMARY KEY and can be used interchangeably.

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.
   The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

Create a Table in MySQL (Part -4)

 A MySQL table stores and organizes data in columns and rows as defined during table creation.

In the process of creating a table, you need to specify the following information:

• Column names – We are creating the title, genre, director, and release year columns for our table.
• Varchar of the columns containing characters – Specifies the maximum number of characters stored in the column.
• The integer of the columns containing numbers – Defines numeric variables holding whole numbers.
• Not null rule – Indicates that each new record must contain information for the column.
• Primary key – Sets a column that defines a record.

SQL in RDBMS (Part -3)

RDBMS (Relational Database Management System) is one of the types of DBMS which shows Relational between the Tables (where data are stored in the form of ROWS & COLUMNS).

In other words, Relational database is a type of database that allows us to identify and access data in relation to another piece of data in the database. It stores data in rows and columns in a series of tables to make processing and querying efficient.

Rows (Tuples) are Horizontal while Columns (Headers are called Attributes – ID, Name, etc.) are Vertical in a Table.

Degree of Relation = No. of Columns

Cardinality = No. of Rows

Data present inside the Column is called ‘Domain Values’.

Databases in SQL (Part -2)

Data usually comes through raw format in an unorganized manner and processing of such data is called ‘Information’.

Our Data are stored in Databases [db] consists set of Tables (Rows & Columns). A Table is an organized collection of data stored in the form of Rows and Columns. Columns can be categorized as vertical, while Rows as horizontal. The Columns in a table are called Fields in records while the Rows can be referred to as unique Records.

For example, a Table that contains Employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, etc.

A Computer can have one or more than one instance of SQL Server installed. Each instance of SQL Server can contain one or many databases. Within a database, there are one or many object ownership groups called schemas. Within each schema there are database objects such as tables, views, and stored procedures. Some objects such as certificates and asymmetric keys are contained within the database, but are not contained within a schema.

SQL Server databases are stored in the file system in files. Files can be grouped into file-groups. 

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in file-groups for allocation and administration purposes.

The number of tables in a database is limited only by the number of objects allowed in a database (2,147,483,647). A standard user-defined table can have up to 1,024 columns. The number of rows in the table is limited only by the storage capacity of the server.

SQL vs MySQL: Key Difference (Part -1)

SQL extends for Structured Query Language that enables the user to design and manage databases, while MySQL is a Relational database management system that allows a user to store and retrieve data from the database.

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MySQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

Both the technologies work on the concept of storing data as per schema (table storage). MySQL is inclined more towards selecting the data to facilitate data display, update and save the data again. It is a bit weaker than SQL Server in terms of data insertion and deletion.

Many famous web-based applications and companies use MySQL like WordPress, YouTube, Joomla, etc. SQL is also used by many platforms like MYSQL, Oracle, Microsoft SQL Server, etc.