SQL Constraints.sql

CREATE DATABASE Employees;

use Employees;

CREATE TABLE Employees ( 
EmpID INT NOT NULL, 
Name VARCHAR(50) NOT NULL, 
Department VARCHAR(30), 
PRIMARY KEY (EmpID) 
); 


INSERT INTO Employees
(Name, Department)
VALUES
('Sharada','HR');

INSERT INTO Employees
(Name, Department)
VALUES
(1234,'IT');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'




CREATE TABLE Products ( 
ProductID INT NOT NULL, 
ProductName VARCHAR(50) NOT NULL, 
Price DECIMAL(10, 2) CHECK (Price > 0), 
PRIMARY KEY (ProductID) 
); 

Insert into Products
(ProductID,ProductName,Price)
VALUES
(111,'Soap',-400);
ERROR 4025 (23000): CONSTRAINT `products.Price` failed for `employees`.`products`

Insert into Products
(ProductID,ProductName,Price)
VALUES
(111,'Soap',0);
ERROR 4025 (23000): CONSTRAINT `products.Price` failed for `employees`.`products`

Insert into Products
(ProductID,ProductName,Price)
VALUES
(111,'Soap',100);





CREATE TABLE Students ( 
StudentID INT NOT NULL, 
Name VARCHAR(50) NOT NULL, 
Age INT CHECK (Age >= 18), 
Marks INT CHECK (Marks BETWEEN 0 AND 100), 
PRIMARY KEY (StudentID) 
); 

INSERT INTO Students
(StudentID,Name,Age,Marks)
VALUES
(111,'Saman',17,50);
ERROR 4025 (23000): CONSTRAINT `students.Age` failed for `employees`.`students`

INSERT INTO Students
(StudentID,Name,Age,Marks)
VALUES
(111,'Saman',105,50);
ERROR 1062 (23000): Duplicate entry '111' for key 'PRIMARY'

INSERT INTO Students
(StudentID,Name,Age,Marks)
VALUES
(111,'Saman',20,50);





CREATE TABLE Customers ( 
ID INT PRIMARY KEY, 
Name VARCHAR(50), 
City VARCHAR(50), 
Salary DECIMAL(10, 2) 
);


SELECT ID, NULLIF(Name, City) AS Result 
FROM Customers;
+----+--------+
| ID | Result |
+----+--------+
|  1 | Ramesh |
|  2 | NULL   |
|  3 | Sunita |
+----+--------+
3 rows in set (0.001 sec)

SELECT ID,IFNULL(Salary,5500) AS FinalSalary
FROM  Customers;
+----+-------------+
| ID | FinalSalary |
+----+-------------+
|  1 |     5000.00 |
|  2 |     5500.00 |
|  3 |     7000.00 |
+----+-------------+
3 rows in set (0.001 sec)





CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY,
    Model VARCHAR(50),
    Year INT
);

ALTER TABLE Vehicles
ADD CONSTRAINT chk_year
CHECK (Year >= 2000);

INSERT INTO Vehicles 
VALUES 
(1, 'Toyota', 1995);
ERROR 4025 (23000): CONSTRAINT `chk_year` failed for `employees`.`vehicles`
MariaDB [Employees]> -- Error: CHECK constraint fails

ALTER TABLE Vehicles
DROP CONSTRAINT
chk_year;
Query OK, 0 rows affected (0.010 sec)
Records: 0  Duplicates: 0  Warnings: 0




CREATE TABLE Users(
	UserID INT PRIMARY KEY,
	Username VARCHAR(50) UNIQUE,
	Email VARCHAR(100) UNIQUE
);

INSERT INTO Users 
VALUES 
(1, 'kulani', 'kulani@gmail.com');
INSERT INTO Users 
VALUES 
(2, 'kulani', 'kulani123@gmail.com'); 
ERROR 1062 (23000): Duplicate entry 'kulani' for key 'Username'
INSERT INTO Users VALUES (3, 'john', 'kulani@gmail.com');
ERROR 1062 (23000): Duplicate entry 'kulani@gmail.com' for key 'Email'