Scribd
Upload
2 views3 pages

DBMS - SQL 1

The document provides an overview of SQL commands for creating tables in a Database Management System (DBMS), including syntax and examples for defining table structures and constraints. It details the CREATE TABLE command, various data types, and constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. Additionally, it includes examples of inserting data into tables and practice problems for creating specific tables.

Uploaded by

shinjan280308
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

DBMS - SQL 1

The document provides an overview of SQL commands for creating tables in a Database Management System (DBMS), including syntax and examples for defining table structures and constraints. It details the CREATE TABLE command, various data types, and constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT. Additionally, it includes examples of inserting data into tables and practice problems for creating specific tables.

Uploaded by

shinjan280308
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL commands

To create a table in a Database Management System (DBMS) using SQL (Structured Query
Language), you use the CREATE TABLE command. This defines the table name and the
columns, along with their data types and optional constraints.

Basic Syntax:-
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);

Examples:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(30),
age INT,
dob DATE
);
Explanation:
• student_id is an integer and the primary key (unique identifier).
• name is a variable-length string of up to 100 characters.
• age is an integer.
• dob is a date field.
Fields/Attributes

student_id name age dob


1 Anuska 16 12/11/2008
Records/Tuples
2 Dekiba 17 13/12/2007

Creating a table named employees with the following fields-


1. Employee id – integer and primary key
2. Name – varchar (size=50) and not null
3. Email – varchar (size=50) and unique (cannot be duplicate)
4. Salary decimal (size=10, decimal value 2) and should be a positive number >0
5. Department_id - integer
6. Hire_date- date type and if no value given, the current system’s date will be added

CREATE TABLE employees (


emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10, 2) CHECK (salary > 0),
department_id INT,
hire_date DATE DEFAULT CURRENT_DATE
);
Types of Constraints in DBMS
Constraint Description
Uniquely identifies each record in a table. Must be unique and NOT
PRIMARY KEY
NULL.
Creates a relationship between two tables. References the primary
FOREIGN KEY
key of another table.
UNIQUE Ensures all values in a column are different.
NOT NULL Ensures a column cannot have a NULL value.
CHECK Ensures that values in a column meet a specific condition.
DEFAULT Provides a default value for a column when none is specified.

Summary of Key Constraints


Allows NULL? Allows Duplicates?

(It means you can have


Constraint Can Be Applied On
(It means you can multiple records with
leave this field the same value in this
empty) field)
PRIMARY KEY No No One column
UNIQUE Yes No One or more columns
NOT NULL No Yes One column
CHECK Yes Yes One or more columns
DEFAULT Yes Yes One column
FOREIGN KEY Yes Yes One or more columns

Practice problems:
Create a Books Table
Requirements:
• book_id (Primary Key, Integer)
• title (VARCHAR, not null)
• author (VARCHAR, not null)
• price (DECIMAL, must be > 0)
• published_year (INTEGER)

Create a Students Table


Requirements:
• student_id (Primary Key)
• name (cannot be NULL)
• email (must be unique)
• dob (date of birth)
• enrollment_date (defaults to current date)
Inserting data into the table in a database

General syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Or

INSERT INTO table_name (column1, column2, ...)


VALUES
(value1a, value2a, ...),
(value1b, value2b, ...);

Examples

INSERT INTO students (student_id, name, email, dob)


VALUES (1, 'Alice Johnson', '[email protected]', '2003-04-15');

INSERT INTO students (student_id, name, email, dob)


VALUES (2, 'Bob Smith', '[email protected]', '2002-09-30');

Or

INSERT INTO students (student_id, name, email, dob)


VALUES
(1, 'Alice Johnson', '[email protected]', '2003-04-15'),
(2, 'Bob Smith', '[email protected]', '2002-09-30');

You might also like