Mekki MOURADI

Data Analyst

Rabat, Maroc

Mekki MOURADI
mekkimouradi@gmail.com

À Propos

Data Analyst Junior avec une solide expérience en marketing numérique et des compétences approfondies dans l’utilisation de Microsoft Excel, SQL, Tableau Software et Python.

SQL Projects

Intermediate Relational Database and SQL

Frame 27

Liens

Certificat

Compétences & Outils

SQL
SQL Commands

Description du projet

  • Créer, Modifier et Supprimer des Bases de Données et Tableaux
  • Créer et Supprimer des Clés Primaires, Index et Clés Étrangères

Objectifs de l'analyse

  • Apprendre à manipuler des Bases de Données, Tableaux, Clés Primaires, Clés Étrangères et Index

Étapes de l'analyse

  1. Create and Drop Databases
				
					CREATE DATABASE database_example
DROP DATABASE database_example
  1. Create and Drop Tables
				
					CREATE TABLE merchandise_item (
	merchandise_item_id VARCHAR,
	description VARCHAR,
	unit_price NUMERIC
);

SELECT * FROM merchandise_item

-- to drop the table
DROP TABLE merchandise_item
merchandise_item_iddescriptionunit_price
  1. Insert Into Values
				
					INSERT INTO customer (customer_id, customer_name)
VALUES ('C0000001', 'Harrison Kong'),
	   ('COOOOOO2', 'John Doe');
	   
SELECT * FROM customer
customer_idcustomer_name
C0000001Harrison Kong
COOOOOO2John Doe 
				
					INSERT INTO merchandise_item
VALUES ('BAMBOOBOOK','Bamboo Notebook',200),
	   ('BAMBOOBOOK','Dragon Painting',300);
	   
SELECT * FROM merchandise_item
merchandise_item_iddescriptionunit_price
BAMBOOBOOKBamboo Notebook200
BAMBOOBOOKDragon, Painting300
  1. Creating and Dropping Primary Keys
				
					-- first, we need to update merchandise_item_id to have distinct values
UPDATE merchandise_item
SET merchandise_item_id = 'DRAGONPTING'
WHERE unit_price=300;
-- then we can make the merchandise_item_id as a primary key
ALTER TABLE merchandise_item
ADD CONSTRAINT PK_merchandise_item_id
PRIMARY KEY (merchandise_item_id)
merchandise_item_iddescriptionunit_price
BAMBOOBOOKBamboo Notebook200
DRAGONPTINGDragon, Painting300 
				
					-- confirming edits
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'merchandise_item' AND constraint_type = 'PRIMARY KEY';
-- to drop the pk
ALTER TABLE merchandise_item
DROP CONSTRAINT pk_merchandise_item_id;
constraint_name
pk_merchandise_item_id
  1. Create and Drop Indexes
				
					-- create an index
CREATE INDEX description_idx
ON merchandise_item (description)

-- drop the index
DROP INDEX description_idx

-- create a unique index
CREATE UNIQUE INDEX description_idx
ON merchandise_item (description)

-- insert into merchandise_item a duplicate value in 'description' to confirm the unique index
INSERT INTO merchandise_item
VALUES ('THORSTATUE','Dragon Painting',2500)

ERROR: Key (description)=(Dragon Painting) already exists.duplicate key value violates unique constraint “description_idx” ERROR: duplicate key value violates unique constraint “description_idx” SQL state: 23505 Detail: Key (description)=(Dragon Painting) already exists.

				
					-- the unique index is working, we can now change the duplicate value to add a new value
INSERT INTO merchandise_item
VALUES ('THORSTATUE','Thor Statue',2500)
SELECT * FROM merchandise_item
merchandise_item_iddescriptionunit_price
BAMBOOBOOKBamboo Notebook200
DRAGONPTINGDragon Painting300
THORSTATUEThor Statue2500
  1. Create and Drop Foreign Key
				
					-- create customer_order table
CREATE TABLE customer_order (
	customer_order_id VARCHAR PRIMARY KEY,
	customer_id VARCHAR,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
)
--insert values
INSERT INTO customer_order
VALUES ('D12345','C0000001')

SELECT * FROM customer_order
customer_order_idcustomer_id
D12345C0000001 
				
					-- confirm the foreign key
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'customer_order' AND constraint_type = 'FOREIGN KEY';
constraint_name
customer_order_customer_id_fkey
SQL Projects