Lets Practice Now..!! Database with sql

In the previous post, you have learned how to start with the database….but I think to elaborate this with some examples and also with practice question or query to check how much you learn from it.

Let’s Practice Now..!!Database with SQL

Query 1:

Query to show, use database and create table

Database with SQL

 

Explanation: Database with SQL

  • Firstly we type “SHOW DATABASES” to show all the databases which are already created in MYSQL in your system. Below that you can see all the databases that can be used further or any database can be created except those which are already created.
  • Hence as I told already that these databases can be used so we have “USE DATABASE_NAME” or “USE TEST” (in this case) which means that we are using TEST named database.
  • If your query is correct then you will get the result as same as shown above in the image.
  • Now, to create a table use “CREATE TABLE TABLE_NAME (COLUMN1 DATATYPE CONSTRAINT (if any), COLUMN2 DATATYPE……. COLUMN_n, PRIMARY KEY (COLUMN_NAME))” or in this case same as in image.

 

Practice Question (PQ)

 

PQ-1:

Create a class of your College name and give different fields to it. Try to use a different data type for each field. You can take help from the above-discussed datatypes.

Note: Post your practice query in the comment box to share it with everyone and me also. Let everyone know the different use of datatypes.

Or if you have any query regarding this CREATE TABLE query comment below.

 

Query-2 To describe the table.

 

Explanation:

  • As explained above “how to create a table”, then we must check how our table looks like, what is its structure. So here we use “DESC TABLENAME”.
  • It will tell you about the schema of the table; schema means description about the structure of the database.
  • It will show the fields (column of the table), type (datatype of the column), NULL constraint, key (if any), a default value of those fields.

 

Query: 3

Query all columns (attributes) for every row in the CITY table.

Input Format

The CITY table is described as follows:

Solution:

Explanation:

  • When you want to view or retrieve all the rows of the table use Asterisk (*) sign with SELECT command.

 

Ex-1

HACKERRANK:

Query all columns for all American cities in CITY with populations larger than 100000. The CountryCode for America is the USA.

Input Format

The CITY table is described as follows:

 

Solution:

Select * from city where population > 100000 and countrycode = ‘USA’

Explanation:

  • Here in this query, we have to retrieve all the columns of the table “CITY” with the condition that the population must be “greater than 100000” and the “countrycode is USA”.
  • So started with the “Select *” for all columns and ended with the “where” condition.
  • Applying both the condition with “Population > 100000” concatenated by “and” and applying the second condition “countrycode = USA” to choose the population greater than 100000 having the countrycode USA.

Database Constraints:

The SQL CONSTRAINTS are an integrity which defines some conditions that restrict the column to remain true while inserting or updating or deleting data in the column. Constraints can be specified when the table created first with CREATE TABLE statement or at the time of modification of the structure of an existing table with ALTER TABLE statement.

The SQL CONSTRAINTS are used to implement the rules of the table. If there is any violation of the constraints caused some action not performing properly on the table the action is aborted by the constraint.

Some CONSTRAINTS can be used along with the SQL CREATE TABLE statement.

The CONSTRAINT keyword is followed by a constraint name followed by a column or a list of columns.

The SQL provides following types of CONSTRAINTS:

Constraint Description
NOT NULL This constraint confirms that a column cannot store a NULL value.
UNIQUE This constraint ensures that each row for a column must have a different value.
PRIMARY KEY This constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specified column or combination of two or more columns for a table have a unique identity which helps to find a particular record in a table more easily and quickly.
CHECK A check constraint ensures that the value stored in a column meets a specific condition.
DEFAULT This constraint provides a default value when specified none for this column.
FOREIGN KEY A foreign key constraint is used to ensure the referential integrity of the data. in one table to match values in another table.

 

1) SQL Primary Key:

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

Column name Datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary Key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1, column_name2,..)

  • column_name1, column_name2are the names of the columns which define the primary Key.
  • The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be like.

Primary Key at column level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

2) SQL Foreign Key or Referential Integrity:

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as a foreign key.

Syntax to define a foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name)

For Example:

  • Let’s use the “product” table and “order_items”.

Foreign Key at column level:

CREATE TABLE product
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY,
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

Foreign Key at table level:

CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

 

3) SQL Not Null Constraint:

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create an employee table with Null value, the query would be like

CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10)
);

 

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

 

Syntax to define a Unique Key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE
);

Unique Key at table level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location)
);

 

5) SQL Check:

Constraint:

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied to a single column or a group of columns.

Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the query would be like

Check Constraint at column level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
gender char(1) CHECK (gender in (‘M’,’F’)),
salary number(10),
location char(10)
);

 

Creating Table with Constraints

The commonly used SQL constraints were specified in the previous section (RDBMS and SQL). The constraints can be created along with creating the table.

Figure 1 shows an example of creating a table with constraints specified on different columns

Figure 1: Employee table created with Constraints

The following are the restrictions created on different column values of Employee table with the help of constraints.

 

Explanation:

  • Emp_id – allows only unique and not null values
  • Name – not null values
  • Age – Not null and value greater than 18
  • Salary – If salary value is not specified, then the default value will be 0
  • E_Mail – unique values but can have null values

A Salesforce Developer at AlmaMate Info Tech PVT LTD. An Aligarian and also your query solver in database field.This site mark the difference as Black or White to make you choose the best for you. Don't feel pressurized, feel confident..!!

Leave a reply:

Your email address will not be published.