Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
A comprehensive guide to sql syntax and operations, covering essential concepts like table creation, data insertion, retrieval, and manipulation. It includes practical examples and exercises to reinforce understanding. The manual is particularly useful for students learning database management systems (dbms) and sql programming.
Typology: Study notes
1 / 48
This page cannot be seen from the preview
Don't miss anything!
rd
th
Department of Computer Science & Engineering VISION OF THE INSTITUTE To continually develop excellent professionals capable of providing sustainable solutions to challenging problems in their fields and prove responsible global citizens. MISSION OF THE INSTITUTE We wish to serve the nation by becoming a reputed deemed university for providing value based professional education. VISION OF THE DEPARTMENT To produce employable, self disciplined and competent IT professional capable of providing sustainable solution to problems in the field of Information Technology. MISSION OF THE DEPARTMENT To provide quality knowledge in field of Information Technology and make the students capable to serve the industry and society by their innovative ideas and skills and lead to the contribution towards the overall progress and development over globe. PROGRAM EDUCATIONAL OUTCOMES (PEOs) PEO 1: Learning: Our graduates to be competent with sound knowledge in field of Computer Science & Engineering. PEO 2: Employable: To develop the ability among students to synthesize data and technical concepts for application to software product design for successful careers that meet the needs of Indian and multinational companies. PEO 3: Innovative: To develop research oriented analytical ability among students to prepare them for making technical contribution to the society. PEO 4: Entrepreneur / Contribution: To develop excellent leadership quality among students which they can use at different levels according to their experience and contribute for progress and development in the society.
Department of Computer Science & Engineering PO10: Communication: Communicate effectively on complex engineering activities with the engineering community and with society at large, such as, being able to comprehend and write effective reports and design documentation, make effective presentations, and give and receive clear instructions. PO11: Project management and finance: understanding of the engineering and management own work, as a member and leader in a multidisciplinary environments. Demonstrate knowledge and principles and apply these to one‟s team, to manage projects and in PO12: Life-long learning: Recognize the need for, and have the preparation and ability to engage in independent and life-long learning in the broadest context of technological change. PROGRAM SPECIFIC OUTCOMES (PSOs) PSO1: The ability to use standard practices and suitable programming environment to develop software solutions. PSO2: The ability to employ latest computer languages and platforms in creating innovative career opportunities
Department of Computer Science & Engineering COURSE OUTCOMES (COs) C306. Understand and apply oracle 11 g products for creating tables, views, indexes, sequences and other database objects. C306. Design and implement a database schema for company data base, banking data base, library information system, payroll processing system, student information system. C306.3 Write and execute simple and complex queries using DDL, DML, DCL and TCL. C306.4 Write and execute PL/SQL blocks, procedure functions, packages and triggers, cursors. C306. Enforce entity integrity, referential integrity, key constraints, and domain constraints on database. CO-PO MAPPING PO1 PO2 PO3 PO4 PO5 PO6 PO7 PO8 PO9 PO10 PO11 PO C306.1 3 2 2 1 1 1 C306. 2 2 2 1 1 1 C306. 2 1 1 1 1 1 C306. 2 1 1 1 1 1 C306.5 2 2 2 1 2 1 1 C307 2.2 1.6 1.6 1 1.2 1 1 CO-PSO MAPPING PSO1 PSO C306.
C306 2
Department of Computer Science & Engineering INTRODUCTION A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques. The database management system (DBMS) is the software that interacts with end users, applications, and the database itself to capture and analyze the data. The DBMS software additionally encompasses the core facilities provided to administer the database. The sum total of the database, the DBMS and the associated applications can be referred to as a "database system". Often the term "database" is also used to loosely refer to any of the DBMS, the database system or an application associated with the database. Computer scientists may classify database-management systems according to the database models that they support. Relational databases became dominant in the 1980s. These model data as rows and columns in a series of tables, and the vast majority use SQL for writing and querying data. In the 2000s, non-relational databases became popular, referred to as NoSQL because they use different query languages. A database has broad searching functionality. For example, a sales department could quickly search for and find all sales personnel who had achieved a certain amount of sales over a particular time period. A database can update records in bulk – even millions or more records. This would be useful, for example, if you wanted to add new columns or apply a data patch of some sort. If the database is relational, which most databases are, it can cross-reference records in different tables. This means that you can create relationships between tables. For instance, if you linked a Customers table with an Orders table, you could find all purchase orders from the Orders table that a single customer from the Customers table ever processed, or further refine it to return only those orders processed in a particular time period – or almost any type of combination you could imagine. A database can perform complex aggregate calculations across multiple tables. For example, you could list expenses across multiple retail outlets, including all possible sub-totals, and then a final total. A database can enforce consistency and data integrity, which means that it can avoid duplication and ensure data accuracy through its design and a series of constraints.
Department of Computer Science & Engineering PREFACE Structure Query Language (SQL) is a database query language used for storing and managing data in Relational DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model of database. Today almost all RDBMS (MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the standard database query language. SQL is used to perform all types of data operations in RDBMS.On the surface, a database might seem much like a spread sheet; it has data arranged in columns and rows. But that is where the similarity ends because a database is far more powerful. Dr. Amit Singhal Ms. Neha Ms. Anjali Yadav Ms. Ahimsha
Department of Computer Science & Engineering GENERAL SAFETY INSTRUCTIONS
Department of Computer Science & Engineering GUIDELINES FOR LABORTORY RECORD PREPARATION While preparing the lab records, the student is required to adhere to the following guidelines: Contents to be included in Lab Records:
Department of Computer Science & Engineering INDEX Exper iment Experiment Date of Date of Faculty No. Name Conduction Submission Signature
Department of Computer Science & Engineering GUIDELINES FOR ASSESSMENT Students are provided with the details of the experiment (Aim, pre-experimental questions, procedure etc.) to be conducted in next lab and are expected to come prepared for each lab class. Faculty ensures that students have completed the required pre-experiment questions and they complete the in-lab programming assignment(s) before the end of class. Given that the lab programs are meant to be formative in nature, students can ask faculty for help before and during the lab class. Students‟ performance will be assessed in each lab based on the following Lab Assessment Components: Assessment Criteria-1: Performance (Max. marks = 5) Assessment Criteria-2: VIVA (Max. marks = 5) Assessment Criteria-3: Record (Max. marks = 5) In each lab class, students will be awarded marks out of 5 under each component head, making it total out of 15 marks.
Varchar (n)- A variable character length string with user specified maximum length n. Int- An integer. Small integer- A small integer. Numeric (p, d)-A Fixed point number with user defined precision. Real, double precision- Floating point and double precision floating point numbers with machine dependent precision. Float (n)- A floating point number, with precision of at least n digits. Date- A calendar date containing a (four digit) year, month and day of the month. Time- The time of day, in hours, minutes and seconds Eg. Time ’09:30:00’. Number- Number is used to store numbers (fixed or floating point). DDL statement for creating a table- Syntax- Create table tablename (columnname datatype(size), columnname datatype(size)); Creating a table from a table- Syntax- CREATE TABLE TABLENAME [(columnname, columnname, ………)] AS SELECT columnname, columnname……..FROM tablename; Insertion of data into tables- Syntax- INSERT INTO tablename [(columnname, columnname, ………)] Values(expression, expression); Inserting data into a table from another table: Syntax- INSERT INTO tablename SELECT columnname, columnname, ……. FROM tablename; Insertion of selected data into a table from another table: Syntax- INSERT INTO tablename SELECT columnname, columnname…….. FROM tablename WHERE columnname= expression; Retrieving of data from the tables-
Syntax- SELECT * FROM tablename; The retrieving of specific columns from a table- Syntax- SELECT columnname, columnname, …. FROM tablename; Elimination of duplicates from the select statement- Syntax- SELECT DISTINCT columnname, columnname FROM tablename; Selecting a data set from table data- Syntax- SELECT columnname, columnname FROM tablename WHERE searchcondition; Q1. Create the following tables: i) client_master columnname datatype size client_no varchar2 6 name varchar2 20 address1 varchar2 30 address2 varchar2 30 city varchar2 15 state varchar2 15 pincode number 6 bal_due number 10, ii) Product_master Columnname datatype size Product_no varchar Description varchar Profit_percent number Unit_measure varchar Qty_on_hand number Reoder_lvl number Sell_price number Cost_price number
Experiment No. Theory and Concept Objective:- To implement various DML Operations on table. DML ( Data Manipulation Language) Data manipulation is The retrieval of information stored in the database. The insertion of new information into the database. The deletion of information from the database. The modification of information stored by the appropriate data model. There are basically two types. (i) Procedural DML:- require a user to specify what data are needed and how to get those data. (ii) Non Procedural DML : require a user to specify what data are needed without specifying how to get those data. Updating the content of a table: In creation situation we may wish to change a value in table without changing all values in the tuple. For this purpose the update statement can be used. Update table name Set columnname = experision, columnname =expression…… Where columnname = expression; Deletion Operation:- A delete reQuestionst is expressed in much the same way as Questionry. We can delete whole tuple ( rows) we can delete values on only particulars attributes. Deletion of all rows Syntax: Delete from tablename : Deletion of specified number of rows Syntax: Delete from table name Where search condition ; Computation in expression lists used to select data
Select column name result_columnname, Columnname result_columnname, From table name; Logical Operators: The logical operators that can be used in SQL sentenced are AND all of must be included OR any of may be included NOT none of could be included Range Searching: Between operation is used for range searching. Pattern Searching: The most commonly used operation on string is pattern matching using the operation ‘like’ we describe patterns by using two special characters. Percent (%) ; the % character matches any substring we consider the following examples. ‘Perry %’ matches any string beginning with perry ‘% idge % matches any string containing’ idge as substring. ‘ - - - ‘ matches any string exactly three characters. ‘ - - - % matches any string of at least of three characters. Oracle functions: Functions are used to manipulate data items and return result. function follow the format of function _name (argument1, argument2 ..) .An arrangement is user defined variable or constant. The structure of function is such that it accepts zero or more arguments. Examples: Avg return average value of n Syntax: Avg ([distinct/all]n) Min return minimum value of expr. Syntax: MIN((distict/all )expr) Count Returns the no of rows where expr is not null Syntax: Count ([distinct/all)expr] Count (*) Returns the no rows in the table, including duplicates and those with nulls. Max Return max value of expr Syntax: Max ([distinct/all]expr) Sum Returns sum of values of n Syntax: Sum ([distinct/all]n) Sorting of data in table Syntax: Select columnname, columnname From table Order by columnname;
