Li “Seay, | ; "AQ, 7 — © dalek prom ULY whey name tile "acyl, jo Candee tajnly Select Pam Ute.” a CD updal UTE yt mand alt woly name = Raye >= guedur than a a 4 = emalley han = eye 49 7 — _ _ ——__DBMS Session 1: Appreciate the concept of Database Management System Q1. What is database? Ans. A database is an organized collection of data. Q2. What is DBMS? Ans. DBMS stands for Database Management system. Itis a software thet controls the creation, maintenance and use of a database. Q3. Name two DBMS Software. Ans. Two DBMS Software are : 1 MySQL 2. Oracle 3. Microsoft SQL Server 4. Microsoft Access, 4, What is the difference between Flat file and Relational? Flat File Relational Data is stored in a single table. Data is stored in multiple tables. It is suitable for less amount of data. Its suitable for targe amount of data Q5. What do you mean by database servers? Ans. Database servers are dedicated ‘computers that hold the actual databases and run only the DBMS and related software. Q6. Write four advantages of database. Ans. Four advantages of database are 1 It reduces Data Redundancy 2. It allows sharing of data. 3. It help to maintain data integrity. 4. It provides Data Security. Q7. What do you mean by Data Redundancy? ‘Ans. Duplication of data ina database is known as data redundancy. Q8. What do you mean by Data Integrity? Ans. Data integrity means that the data is accurate and consistent in the database. Q9. What is Data Consistency? Ans. Data Consistency means there shoud be multiple mismatching copies of the same data in a database. Q10. Write two features of database. Ans. Two features of database are : 1. A database can have one or many tables. 2. Every table in a database has a primary key field which ensures 00% unique values in the database. Q11. What do you mean by RDBMS. Ans. RDBMS stands for Relational Database Management System. When data is to be stored, maintained, and retrieved from multiple tables then special database software are required known as RDBMS. Q12. What is Primary Key? ‘Ans. A field which is unique for each and every record in a table is called pimary key. Q13, What do you mean by Composite Primary key? Ans. When Primary key constraint is applied on one or more columns then it is known as Composite Primary Key.014. Write the queries for the following table : Emp Emp_id | Ename | Salary 1 Suman} 20000 2 Sanjay | 32000 3 Ravi 30000 tl it 100. «a Display the salary of all the employees after incrementing by Rs 10 ‘elect Salary +1000 from Emp; eee ea! a Display the Employee id and salary of all the employees after decreasing by Rs Ans. Select Emp_id, Salary ~ 500 from Emp; . ina it i amount c. Display the Name and salary of all the employees after incrementing it as thrice the of present salary. Ans. Select Ename, Salary * 3 from Emp; 4. Display the Employee id, Name ana salary of all the employees after decrementing it as half the amount of present salary. Ans. Select Emp_id, Ename, Salary/2 from Emp; © Display the Employee id and Name of all the employees. Ans, Select Emp_id, Ename from Emp: (AS. Write the queries for the followina table : Student Aamno Name Class House 1001 Sonam 9 | Blue 10902 Ravi 10 Yellow 1003 Poonam 10 Green «2 Display the entire ble ‘Ans. Select * from Student ! b. Display the list of students whose house color is blue. ‘Ans. Select“ from Student where House = “Blue” & Display the admission number of studénts Ans. Select Admno from ‘Student where House those house color is green. Son iam records in ascending order of AdmisSlon Naminer, ‘Ans. Select from Student order by Admno Asc. ¢. Display the records of Class 10 Students, ‘Ans. Select“ from students where Class = 10; {Display the class of Ravi* Select Cizss from Student where Name = ‘Ravi 9 lnsert the given record : 1004, “Aman”, 4 ‘Ans. inser wto Student vaues( 1004, “Amant i “Blue” 1, Blue") Q16. Which Is used for the follo ne insert @ new record ‘wing task In database? © modify the existing data, 7 Gelete a record © display record Ans. Insert Update DeleteQi7. Write the queries for the following table ; Item ltemno Iname Price | Qty 12 Pen 10 7 13 Eraser 5 15 — 14 ‘Notebook 15 20 a, Write a query to Insert a new record of following detalls 15, “Pencil”, 20, 10 Ans. Insert into item values(15, “Penci", 20, 10); b. Write a query to display detail of items whose quantity Is Ans. Select * from Item where Qty > 10; more than 10. c. Write a query to change the quantity of Item number 13 to 25. Ans. Update Item set Qty = 25 where Itermno = 13° d. Display the total amount of each Item. The amount must be calculated as the price muttiptied by quantity for each item Ans. Select Price * Qty from Item. e. Display the name of item whose price is 10. Ans. Select Iname from Item where price = 10 ; £. Display all the records in ascending order of price. Ans. Select * from Item order by Price asc. 5 g. Identify the Primary key from table item. Ans. ftemno h. Write the suitable data type of field “Iname”. ‘Ans, Char or Varchar i. Write a query to increase the price of all items by Rs2. Ans. Update tem set Price = Price + 2; J. Write a query to decrease the price of all items by Rs2 whose price is less than 20. ‘Ans. Update Item set Price = Price ~ 2 where Price < 20; ‘Session 2 : Create and Edit Tables using Wizard and SQL Commands. Qt. What do you mean by RDBMS? ‘Ans. ROBMS stands for Relational Database Management System. It is a database management system which is based on relational model. Q2. Define the following terms : 1 Table 2 Fields 3. Records Ans. Table : A table is a set of data elements that is organized in vertical columns and horizontal rows. Fields - A field is a set of data values of a particular simple type, one for each row of the table. Records : A row also called @ Record or Tuple represents a single, data item in a table. eer ace tna tne ways of creating table in OpenOffice Base. ‘Ans, Two ways o creating table OpenOffice Ba a. Create table in design vew seme ©: Use Wizard o create able ‘Q4. What do you mean by Data type? Give example of two dat at : ta type used In OpenOffice Base, Ans. Datatypes are used 10 identify which type of data (value) we are going to store in the database.decimal value : Q8. Identify the data type in which we can store the a. Boolean b. Tinyint c. Decimal d. Real . Double £. Smaltint Ans. Data types in which we can store the decimal value are c. Decimal d. Real e. Double 38. What is the difference between Char and Varchar data type? ‘Ans. Charis a fixed length data type and Varcharis a varlatle length data type. QZ. Write the data type suitable for the following fields. a. DateofBirth b. Name ©. Rollno 4. Class e. Email F. City Ans. a. DateotBirth ~ Date b. Name — Char or Varchar ¢- Rolino —Tinyint / Smalint/ integer . Class - Char / Varchar e. Email ~ Char or Varchar . City — Char or Varchar 8. Write four data types available in Alphanumeric Data types. 22S. Four data types available in Alphanumeric Date types : LongVarchar Char Varchar \Varchar_Ignorecase 28. Data in a (RDBMS) Is organized in the form of : Ans. Tables. — / Q10. In RDBMS table is also.known as. Ans. Relation oo Q11. Write four data types which are Ans. Four data types which are used to Smatiint Sed to store numerical value. . Store numerical value are (212. Write four appropriate field names and thelr data ty Pes to store record of student, Ans. Four appropriate field names and their data \ypes to store record of students are 1 Name Char/ Varchar 2. Admno 3. Fees ———__FioatDouble 4, DOB ——___pateQ14, What is the default data type of Taniview? ‘Ans. Verchar 'ype of a field while creating table in design view ts. are the basic buildi f a data: os. ic building blocks of a database. Q16. Write the expanded form of SQL. Ans. SQL : Structured Query Lariquage Q17. A table has columns (horizontal / Vertical). ‘Ans. Horizontal , Vertical (horizontal | Vertical) row and _____—— What is SQL? © SQL stands for Structured Query Language + SQL lets you access and manipulate databases: «= SQL became a standard of the American National Standards Institute (ANSI}.in 1986, and of the International Organization for Standardization {1$0) i in 1987 What Can SQL do? + SQL can execute queries against a database + SQL can retrieve data from: database SQL can insert records in a database © SQL can update records.ina database * SQL can delete records from a database * SQL can create new databases * SQL can create new tables in a database * SQL can create stored procédures in a database + SQL can create views in a database + SQL can set permissions on tables, procedures, and views Some of The Most Important SQL Commands + SELECT - extracts data from a database * UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database * CREATE DATABASE - creates a new database* ALTER DATABASE - modifies a database + CREATE TABLE - creates a new table + ALTER TABLE - modifies a table * DROP TABLE - deletes a table SQL CREATE TABLE Statement varchar(255)); SREATE TABLE Persons (PersoniD int, LastName varchar(255), FirstName varchar(255) Address varchar(258),Cty varchar(255)) ‘The SQL INSERT INTO Statement INSERT INTO table_name VALUE! S (value1, value2, value3, 'NSERT INTO Customers VALUES (‘Cardinal', "TomB.Erichsen'’, ‘Skageh21’, 'stavanger', '4006', ‘Norway'}; . & Insert Data Only in Specified Columns! 'NSERT INTO Customers (CustomerName, City, Country) VALUES (‘Cardinal ‘Stavanger, ‘Norway'); SQUUPDATE: tement a UPDATE Customers SET ContactName ‘Alfred Schmidt, Gity= ‘Frankfurt’ WHERE CustomerID = 1; UPDATE Customers SET ContactName="luait! WHERE Coutithy="Mexico'; “ SQL DELETE, Statement DELETE FROM Customers WHERE CustomerName s Futterkiste’; OELETE FROM Customers; Delete a Table DROP TABLE Customers; ‘ The SQL SELECT Statement SELECT column1, column2, ... FROM table_name; Select ALL columns SELECT * FROM Customers; The SQL WHERE Clause untry="Mexico' * FROM Customers WHERE Customer0=1; SELECT * FROM Customers WHERE Customerip > 80; SELECT * FROM Customers WHERE Co SELECT The SQL ORDER BY ‘SELECT * FROM. Products ORDER BY Price; SELECT * FROM Products ORDER BY Price DESC; SELECT * FROM Products ORDER BY ProductName SELECT * FROM Products ORDER BY ProductName DE sc;ORDER BY Several Columns ORDER BY Several Columns SELECT * FROM Customers ORDER BY Country, CustomerName; SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC; The SQL AND Operator SELECT * FROM Customers WHERE Country = 'Spain’ AND CustomerName LIKE 'G%'; SELECT * FROM Customers WHERE Country = 'Germany' AND City = ‘Berlin’ AND PostalCode > 12000; The SQL AND Operator SELECT * FROM Customers WHERE Country = 'Germany' OR Country = 'Spain’; SQL MIN() and MAX() Eunctions SELECT MIN(Price) FROM Products; : SELECT MAX(Price) FROM Products; SELECT AVG(Price) FROM Products; SELECT * FROM Customers WHERE Cust Return all customers from a city that contains the letter L: SELECT * FROM Customers WHERE city LIKE “96L%'; SELECT * FROM Customers .E Customer! = s a SELECT * FROM Products WHERE Price BETWEEN 10 “AND:20; SQL ALTER TABLE statement ALTER TABLE Customers ADD Email varchar(255); = ALTER TABLE - DROP COLUMN3 ALTER TABLE Customers DROP-COLUMN Email; ated: TABLE - ALTER/MODIFY DATATYPE ALTER TABLE table_name MODIFY COLUMN column: hame datatype; on