This is to certify that ISM Practical lab File is the original work of KHUSHAL SHARMA (05116701722) student of BBA(G) 5th semester and has duly completed his project under my guidance and supervision to my satisfactory level. This work has been done in partial fulfillment of the requirement for the award of the degree of Bachelor of Business Administration from SIRIFORT INSTITUTE OF MANAGEMENT STUDIES, GGSIPU and has not been submitted in any other university for the award of any degree.
MR. ANUKOOL BAJPAI SIR
(PRINCIPAL) KHUSHAL SHARMA ENROLL NO. 05116701722
2 ACKNOWLEDGMENT
It is not possible to prepare the project report without the assistance and encouragements of other people. This one is certainly no exception. On the very outset of this report, I would like to extend my sincere and heartfelt obligation towards all the personages who have helped me in this endeavor. Without their active guidance, help, cooperation and encouragement. I would not have headway in the project. I am extremely thankful and pay my gratitude to my faculty MR. ANUKOOL BAJPAI SIR for his valuable guidance and support on completition of this project in it presently. I also acknowledge with a deep sense of reverence, my gratitude towards my parents and my friends, who has always supported me morally. Any omission in this brief acknowledgment does not mean lack of gratitude.
Thanking You Khushal sharma
3 QUESTION 1: Introduction to SQL. Explain what are different types of data types in SQL.
ANSWER: SQL, or Structured Query Language, is a domain-specific language used for
managing and manipulating relational databases. It enables users to interact with databases by defining and manipulating data. SQL is essential for tasks like querying data, updating records, inserting new data, and more. The language is standardized, allowing for portability across different database systems. Common operations include SELECT (retrieve data), INSERT (add new data), UPDATE (modify existing data), and DELETE (remove data). Understanding SQL is crucial for anyone working with databases or involved in data management.
Different types of Data Types in SQL
1) CHAR- A fixed length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters can be from 0 to 255. Default is 1.
2) VARCHAR - A variable length string (can contain letters, numbers, and special characters).
3) BOOLEAN - The BOOLEAN data type supports the storage of two values TRUE AND FALSE.
4) INTEGER- The INTEGER data type accepts numeric values with an implied scale of zero.
5) DECIMAL - The DECIMAL data type accepts numeric values, for which you may define a precision and a scale in the data type declaration.
6) FLOAT - The FLOAT data type accepts approximate numeric values, for which you may define a precision up to a maximum of 64. If no precision is specified during the declaration, the default precision is 64. Attempting to assign a value lager than the declared precision will cause an error to be raised.
QUESTION 2: Explain SQL DDL Commands. With Syntax and Examples.
ANSWER: SQL DDL (Data Definition Language) commands are used to define, manage, and control the structure of a database. They allow you to create, modify, and delete database objects. Here are some essential DDL commands:
1. CREATE: used to create the databases, the tables, and the columns within each table. Within the create statement we also define the data type of each column. A data type is literally the type of data we are supposed to store within each column, whether it be an integer, a date, or a string.
Syntax- CREATE TABLE TABLE_NAME (Attribute_name1 Data type, Attribute_name2 Data
type)
4 Example- CREATE TABLE STUDENT (Name Varchar (50), Age int)
CREATE DATABASE: Creates a new database.
CREATE TABLE: Creates a new table with specified columns and data types.
CREATE INDEX: Adds an index to a table, improving search performance.
2. ALTER: used to alter existing database structures. This includes adding columns and more. Syntax- ALTER TABLE TABLE_NAME ADD COLUMN Attribute name Data type; Example- ALTER TABLE STUDENT ADD COLUMN Last Name varchar (50)
ALTER TABLE: Modifies an existing table, such as adding or dropping columns.
ALTER INDEX: Modifies an existing index.
3. DROP: This is used to destroy your database or table.
Syntax- DROP TABLE TABLE_NAME Example- DROP TABLE STUDENTS
DROP DATABASE: Deletes an existing database.
DROP TABLE: Removes an existing table and its data. DROP INDEX: Deletes an existing index.
4. TRUNCATE: This is used to rename.
Syntax- RENAME TABLE OLD_TABLE_NAME TO NEW_TABLE_NAME Example- RENAME TABLE Students TO Students1
TRUNCATE TABLE: Removes all rows from a table but retains the table structure for future use.
5. RENAME: This is used to rename.
Syntax- RENAME TABLE OLD_TABLE_NAME TO NEW_TABLE_NAME; Example- RENAME TABLE Students TO Students1
RENAME: Changes the name of an existing table.
6. COMMENT: COMMENT ON: Adds comments to database objects for documentation purposes. DDL commands are powerful and can have a significant impact on the database structure. It's important to use them with caution, especially when dealing with live production databases, as they can result in data loss or other unintended consequences if not used carefully.
QUESTION 3: EXPLAIN SQL DML COMMANDS. With Syntax and Examples.
5 ANSWER: SQL DML (Data Manipulation Language) commands are used to interact with and manipulate data stored in a relational database. The main SQL DML commands are:
1. SELECT: Retrieves data from one or more tables. It is used to query the database and retrieve specific information. This is used to select data from our database. We first SELECT AND then we say what columns to select. After we say what columns, we specify what tables using FROM. After we select what columns and what tables we can limit our results using a WHERE clause. Syntax- SELECT * from TABLE-NAME, WHERE CLAUSE.
2. INSERT: Adds new rows of data to a table. It is used to insert new records into the database. This is used to change values. Syntax- INSERT INTO TABLE_NAME VALUES (Value1, value2…); Example- INSERT INTO STUDENT values (01,’Umesh’,25)
3. UPDATE: Modifies existing data in a table. It allows you to change the values of one or more columns in existing rows. This is used to change values. Syntax- Update Table Name SET Attribute Name=Value where CLAUSE; Example- Update STUDENT SET Name=Amit where p_id=05;
4. DELETE: Removes rows from a table. It is used to delete records from the database. This is used to delete values (the database structure stays the same, only inserted values are removed). Syntax- delete from table_ name where clause. Example-delete from STUDENT where Name=’Umesh
These commands are essential for managing the data within a relational database, providing the ability to retrieve, add, update, and delete records as needed.
QUESTION 4: Create table customer (c_id, c_name, address, city, pinocde, country) insert at least 10 values. Display the table
6 ANSWER :
QUESTION 5: Create table named employee containing columns
(emp_id,emp_name,emp_desig,dob,emp_sal,emp_dept) insert 10 at least values Constraints applied on employee table
7 A. .emp_id should be primary key B. emp_sal should not contain any null value C. emp_desig should be unique
ANSWER :
QUESTION 6: Using the table of experiment 5, Perform various SQL Commands.& Display Table A. Add column in above table date of joining, experience. B. Complete the table definition
8 C. Delete the column DESG D. Find out details of an employee whose salary is above 25000.
ANSWER :
9 10 QUESTION 7: Create the following table AND PERFORM SQL COMMANDS STUDENT (ROLL_NO, NAME, AGE, COURSE, MARKS) A. Select * from student where age is > 18 & course = MBA B. Find out total number of records in table C. Display name and course of student D. Find out the average of all the marks. Display it as average marks.
ANSWER :
11 QUESTION 8: What do you understand by ER Modelling, and explain all the symbols of ER model with example?
ANSWER
12 QUESTION 9 : What is relationship and cardinality in ER model explain with examples. ANSWER : Within entity-relationship diagrams, relationships are used to document the interaction between two entities. Relationships are usually verbs such as assign, associate, or track and provide useful information that could not be discerned with just the entity types.
Cardinality is the number of relationships or instances one entity has with another. Types of Cardinality Ratios- There are 4 types of cardinality ratios-
