Thursday, 7 May 2015

HTML & XML

INFORMATICS PRACTICES
CLASS XII
UNIT –Web Application Development


Q1. What do you understand by the following?
(i) URL  The full form of URL is Uniform    Resource Locator. A URL  refers to an address on the internet. Billions of documents/multimedia files can be accessed  on internet through their URLs

(ii) Multimedia:- Multimedia refers to usage of multiple media such as text, photographs , graphics, video and audio at the same time. A multimedia file is a combination of media like text, photographs, graphics, video and audio.

Q2. What is protocol? How do protocols affect Internet?
Ans:- A protocol is set of rules governing an operation or application.
On internet, various protocols play their roles e.g network addressing on internet is based upon a protocol called Internet protocol. Similarly a particular location on Internet may be accessed using HTTP(Hyper Text Transfer Protocol)

Q3. Differentiate between web browser & web server
Ans:- A WWW client is called a web browser and WWW server is called Web Server
A web browser navigates through the world wide web and displays pages. The web browser requests a page from a web server. The web server makes available the desired page to web browser

Q4. In the URL http://www.mycorp.com/pr/master.htm, what is the http component
Ans:- http specifies the type of server

Q5. . In the URL http://www.mycorp.com/pr/master.htm, what is the www.mycorp.com component
Ans:- www.mycorp.com: is address of the server

Q6. In the URL http://www.mycorp.com/pr/master.htm, what is the /pr/master.htm component
Ans:-/pr/master.htm is the path of the file master.htm.

Q7. How is file communicated to another computer on internet?
Ans FTP (File Transfer Protocol) is employed for transferring files from one computer to another on internet.

Q8. HTML stands for……………..
Ans Hyper Text Markup Language.



Q9. What to understand by tag?
Ans: A tag is a coded HTML command that indicates how  part of a web page should be displayed.

Q10. What is an attribute in HTML?
Ans: An attribute is a special word used inside an HTML tag and which specifies, additional information to tag such as color, alignment, height or width.

Q11. Define the tag,<HTML>
Ans:- The <HTML> tag identifies the documents as an HTML document. An HTML document begins with <HTML> and ends with </HTML>.

Q12. HR tag is used for……….
Ans:- Separating sections of a document visually.

Q13. <TITLE> tag is used to define…………..
Ans:- document’s title

Q14. What is the basic structure of HTML File?
Ans:- <HTML>
          <HEAD>
          <TITLE>  Title of page is written here  </TITLE>
           </HEAD>
           <BODY> 
                       The HTML tags that define your page go here
           </BODY>
           </HTML>

Q15. Face attribute is used with ……..tag and used for……………….
Ans:- FONT, changing font type of the text.

Q16. To align multiple lines of text we use………..
Ans Align

Q17.What is the use of ‘size’  in <BASEFONT> tag?
Ans:- Attribute  size, determines the document’s base font size. The size can have an absolute value from 1 to 7 e.g size=5 or as a relative value by placing a plus or minus sign before the value.

Q18. List and define different types of paragraph alignments.
Ans:- Paragraph  alignment can be either LEFT (Left to Right) or Right(Right to Left) or CENTER.

Q19. The value of #FFFFFF is
Ans White

Q20. The value of #000000 is
Ans Black

Q21. The default alignment text is________________ default text color is_____________ and the default background color is__________
Ans Left, black, grey

Q22. The tags that require a starting as well as ending tag are called________________
Ans Container Element

Q23. To specify the background image for the document, Background attribute is used with which of the following tags?
Ans:- <BODY>

Q24. To define the basic font size, which of the following tag is used?
Ans:- BASEFONT

Q25. What is the use of <CENTER> tag in an HTML document?
Ans:- <CENTER> tag is used to center align text and an image.

Q26. Which html tag and corresponding attributes are used to include an image as the background of an html document?
Ans:-<BODY> tag and Background attribute

Q27.How is <P> tag different from <BR> tag in HTML?
Ans:- <P> tag inserts a blank line and starts a new paragraph
<BR> tag forces text to anew line like the <P> tag but without inserting a blank line.

Q28. The textual comment that appears in the source HTML document but is not rendered by browser is called……….
Ans:- Comment

Q29. Comments are enclosed in… and ….. tags
Ans:- [<!--,- - >]

Q30. By default <HR produces a________rule
Ans:- [3-D] rule

Q31._________text styles are handled in its own way by the browser
Ans:-[Logical]

Q32. To display an    (and Symbol) on the Web page,…. Is used in the source HTML code
Ans:-[&amp;]

Q33. Two tags break the line flow. These are……
Ans:- [BR>, <HR>]

Q34. Background color is set by attribute and background graphic is set by……. Attribute of ….tag
Ans:-[Bgcolor, Background, Body]

Q35. What type of lists are supported by HTML
Ans:-Unnumbered, Numbered, Definition

Q36. Which three tags lets you create the definition lists
Ans:-A definition list(coded as <DL>) usually consists of altering a definition term(coded as <DT>) and a definition description (coded as <DD>)

Q37.Which two tags lets you create the unnumbered lists and numbered lists
Ans-<UL> and<LI>
        <OL> and <LI>

Q38 What is a  table ?Which tag is used to create tables in HTML?
Ans:- Tables: Data arranged into rows and columns of cells
An HTML table is presented and manipulated with the help of these tags
   <TABLE>, <TBODY>,<THEAD>,<TFOOT>,<TR>,<TD>, and <TH>.

Q39. Which attributes are used to give border to a table?
Ans:- BORDER attribute to specify border
BORDERCOLOR attribute to specify color of border
FRAME attribute to state which portion of border will render
RULES attribute deals with inside border edges

Q40. Which attribute let you control the display of select border sides of a table.
Ams:- FRAME attribute

Q41. Which attribute is used to control the inside table border
Ans:-RULES attribute

Q42. How is spacing in cells of table controlled
Ans: To control the spacing in cells, the CELLSPACING and CELLPADDING attributes can be used.

Q43. How can a 2D or #D horizontal rule be displayed
Ans 3D horizontal rule can be displayed using <HR> tag. To have a flat 2D rule just add noshade to <HR> tag

Q44. which tag is used to specify
(i)                 table data  <td>
(ii)               (ii) table header <th>
(iii)             (iii) table row <tr>
Q45. Name the attributes used for following
Ans:
(i) setting the cell width                      WIDTH
(ii)setting cell’s background color       BGCOLOR
(iii)setting cell’s background image    BACKGROUND
(iv) changing the cell span                   ROWSPAN and COLSPAN
(v) aligning cell contents vertically     VALIGN

Q46. A set of header rows is defined using___________ tag
Ans:- <THEAD>

Q47. what are forms?
Forms provides a means by which one can  prompt the web surfer for information, and allow the surfer to enter the information into the web page.

Q48. What do you understand by controls in forms?
Ans:-Users interact with forms through some special elements called controls

Q49. Name different control types supported by HTML forms
Ans:- HTML defines these control types: buttons, checkboxes, radio buttons, menus, text boxes, text areas, password control and hidden controls.

Q50Write the tags to define the following:-
Ans:-
(i)                 Text Box

<INPUT type =”text” name=”name of the text box”>
(ii)               A text area

<TEXT AREA rows = “rows here cols = “columns here “ name = “name of text area here”>
(iii)             A radio button

<INPUT type = “radio” name = “ rbutton” value = “radio button”>
(iv)             A check box
<INPUT type = “checkbox” name = “ nameOf The checkbox “ value = “the valueTobeused”>
(v)               A password box
<INPUT type = “password “ name =” <name of the password box”>
(vi)             A submit button
<INPUT type =”submit” name = “submit “ value =”submit”/>
(vii)           A label
<LABEL for = label “> content here   </LABEL>



Q51. Expand the following:
Ans:XML:- Extensible Markup language
        EDI:- Electronic data Interchange
        CSS:- Cascading Style Sheet
        DTD:-Document Type Definition

Q52. What is markup Language?
Ans:- A markup language is set of rules/tags that define the structure and format of text while presenting text.

Q53. what is XML?
Ans:- XML is eXtensible markup language which allows to create application specific structured documents by allowing creation of new tags. These structured documents can later be rendered(i.e. presented in human understandable manner) in different ways.

Q54. Name some markup languages based on XML?
Ans:- InkML- Ink Markup Language
          RecipeML:-Recipe Markup Language

Q55. What is prolog?
Ans:- The Prolog is a preface or introduction to XML document. It can contain upto five option components. The grammar structure of the XML document is defined under prolog.

Q56. What is data instance?
Ans:- The data instance consists of real data and its description through elements. It is also known as body of XML document.

Q57. What is element? What is attribute?
Ans:- Elements are means to define individual data items. An element begins with a start tag and ends with an end tag.
Attributes are the property settings of element given through name values pairs.

Q58.What is DTD?
Ans: DTD is set of rules that defines what tags appear in an XML document, so that viewers of XML document know what all the tags mean. DTDs also describe the structure of a document.

Q59. What is a valid document?
Ans:- A valid XML document is the one that is well formed and confirms to the specifications of DTD. In other words, valid documents must confirm not only to the syntax, but also to DTD.

Q60. Name a predefined markup language?
Ans:- HTML

Q61. Name a meta language?
Ans:- SGML and XML

Q62. Can an XML document be well formed but not valid or vice versa?
Ans:- An XML document can be well formed but still not valid, but each valid document has to well  formed.

Q63. What all tags are required in every HTML page?
Ans:- <HTML>, <HEAD>, <TITLE> and <BODY>

Q64. What is root element? What is its significance? What is its alternate name?
Ans:- XML documents require that there must be one element which is parent of all other elements in data instance.
The parent element of all other elements in data instance is known as root element or document element.

Q65. How are tags used in XML different from tags in HTML? Write two points.
Ans :- XML
·         New tags can be created using XML
·         XML tags cannot be empty tags.

HTML
  • New tags cannot be created using HTLM tags
  • HTML tags can be empty tags.

Q66. What is wrong in the following coding?
(i)                 <HEAD><my web page>
<TITLE> welcome to my web page
  </HEAD>
  </TITLE>
      Ans:- <my web page>  is not an HTML tag

(ii)               <FONT name= “Arial”, type =”Bold” size=3>
Ans:-  name is not an attribute of tag <FONT>

(iii)             <FONT face = comic sans ms color= Red>
Ans:- attribute values should be in double codes

(iv)             <FONT color= #345678
Ans:- Ending of tag is missing

(v)               <BODY color = “Red” background = “Myimage .jpg”>
Ans:- color is not an attribute of <BODY>

(vi)             <BR  </BR>
Ans:- <BR> represents single action and do not have beginning or ending.
(vii)           <P Font face = “Arial” color = “Blue”>
      Ans:-<P> and <FONT> have not been combined correctly.

       (viii)<Body Margin top = 60 left =75>
        Text with changed margin </Body>
Ans:- Margin Top and Left are not attributes of tag<Body> . For given Top margin TopMargin attributes should be used and for setting Left margin attribute should be used.

(ix) <BASEFONT SIZE =5>
        <BODY> Text with New format </BODY>
Ans:- <BaseFont Size =5> should come after <BODY>

(x) <HTML>
      <HEAD>
       <TITLE> New Page
       </HEAD
       </TITLE>
       </HTML>
Ans:- Ending </TITLE> of title tag should come before ending of head tag i.e. </HEAD>

Q67. Differentiate between <TITLE> and <HEAD> tag
Ans:- The HEAD tag is used to define the document header. The HEAD tag contains information about the document including its title, scripts used, style definitions and document descriptions.
The title element contains your document title and identifies its content in a global context. The title is typically displayed in the title bar at the top of the browser window.
To add <HEAD> tag we enter it between <HTML> tags ; to add <TITLE> tag we enter in between <HEAD> tag.

Q68. Differentiate between container and empty tags.
Ans:- Container elements require pair tags  i.e a staring as well  as an ending tag i.e <TITLE>…<?TITLE>, <HEAD>…</HEAD>.
Empty elements require just a starting tag and not an ending tag e.g. <BR>, <BASE>.

Q69. List and define different types of heading tags.
Ans. Different types of heading tags are <H1>, <H2>, <H3>, <H4>,<H5>, and <H6> each one representing different levels of headings with <H1> being  the largest.

Q70. List and explain the different attributes of body tag
Ans:-
(i)                 background:- to load an image and using it as a background when displaying a page.
(ii)               bgcolor:-to change background color
(iii)             text:-to change color of text.
(iv)             link:-to change color of links
(v)               vlink:-t change color of visited links
(vi)             alink:-to change color of active links.
(vii)           leftmargin:-to leave some blank area in the left side.
(viii)         topmargin:-to set the top margin i.e distance from top edge.

Q71. What is the difference between <BASEFONT>  and <FONT> tag?
Ans:- The <BASEFONT> tag lets you define the basic size for the font, the browser wil use to render normal document text i.e the text for which no other font size setting has been provided. The <FONT> tag  lets you change the size, style and color  of text It is generally used for changing  the appearance of a short segment of text.

     Q72. What is the role of ALIGN attribute of <TABLE> tag
Ans:- To align a table , ALIGN attribute of <TABLE> tag may be used. This can have values left, right and center indicating that that the table should be placed against the lefty or right margin of the text flow, with the text flowing around the table, or in the middle with the text flow above and below.

Q73. Differentiate between ordered and unordered lists?
Ans:- The <UL> tag is used for defining unordered list(or unnumbered list). In these lists a special bullet symbol appears in front of every list item.
The <OL> tag is used for defining ordered lists. In these lists a number or letter appears in front of every list item.

Q74. Write HTML code to produce these controls:
Ans:- (i) a text box :-
<INPUT type= “text” name = “name of the text box”>
(ii)a text area with 10 rows and 30 columns:-
<TEXT AREA rows=”10” cols =”30” name =”Tarea”>
      Enter description here
       </TEXT AREA>
(iii)A password text box
Ans:- <INPUT type = “password “ name = “ name of password box”>

Q75. Compare HTML and XML briefly?
Ans:-  HTML
  • HTML documents formats and displays web pages data
  • HTML tags are predefined
  • HTML tags may not have closing tag
  • HTML tags are not case sensitive
  • HTML document documents are directly viewable in a browser.

XML
  • XML documents carry data along with their description
  • XML tags are not predefined
  • XML tags must have a closing tag
  • XML tag are case sensitive.
  • XML document can be viewed  only if proper CSS/XSL file is also available along with XML file.

Q76. What is the significance of root element? What is its alternative name?
Ans:-XML documents require that there must be one element which is parent of all other elements in data instance.
The parent element of all other elements in data instance is known as root element or document element.

Q77.  What is declared empty tag?
Ans:- A declared empty tag (or simply empty tag> is a tag without end tag. It is written as the tag name with a following slash e.g. <etag/> can be written in place of writing
<etag>  </etag>

Q78. What is XML document system? How it is different from XML document or XML file?
Ans:- XML document system is the set of all related files and documents that are needed when an XML based file is to be rendered on web. It comprises of :-
(i)                 a stylesheet file (CSS or XSL etc.)
(ii)               grammar structure(DTD).
(iii)             Semantics of data <XML file>

The XML document or XML file is a component of XML document system. An XML document contains the real data and its semantics.

Q79. What do you understand by well formed document?
Ans:- A document that obeys the syntax of XML is well formed document.
A document that includes sequence of markup characters  that cannot be parsed or are invalid cannot be well formed. If all tags in a document are correctly formed and follow XML guidelines, then a document is considered as well formed.

Q80. What do you understand by elements and attributes of XML document?
And:- Elements are means to define individual data items. An element begins with start tag and ends with end tag
The properties of elements cab be described through attributes. Attributes are the property settings of element, given through name value pairs.



………………….XXXXXXXXXXXXXXXX……………………………

Unit 3 Database Concepts & MySQL

INFORMATICS PRACTICES
CLASS XII
UNIT 3 -DATABASE CONCEPTS AND MySQL

Q1. Define the following terms :-
a)      Database:- A database is a collection of interrelated data and database system is basically a computer based record keeping system.
The advantages provided by a database system are:-
i)                    Reduced data redundancy
ii)                  Controlled data inconsistency
iii)                Shared and secured data
iv)                Standardized data & integrated data
b)      Relation:- A relation is a table having atomic values, unique rows & unordered rows & columns.
c)      Tuple:- A row in a relation is known as tuple.
d)     Attribute:- A column of a table is known as attribute.
e)      Domain:- A domain is a pool of values from which the actual values appearing  in a given column are drawn.
f)       Primary Key:- It is a column or a combination of columns that uniquely identifies a row in a relational table.
g)      Candidate key :- All possible combination of columns that can possibly serve as the primary key are called candidate key.
h)      Alternate key:- A candidate key that is not serving as a primary key is called alternate key.
i)        Foreign Key:- A column or a combination of columns whose values are derived from primary key of some other table is called the foreign key of the table in which it is contained.
j)        Schema:- A Schema is a logical collection of related database objects.
k)      Constraint:- A constraint is a restriction applicable on the data contained in a column or a group of columns from one or more tables. Eg:- NOT NULL, CHECK, DEFAULT etc.
l)        Degree:- The number of attributes (columns) in a relation determine the degree of a relation.
m)    Cardinality:- The number of tuples(rows) in a relation is called the cardinality of the relation.

Q2. Compare char and varchar datatypes.
Ans:-   The CHAR datatype stores fixed length strings such that strings having length
           smaller than the field size are padded on the right with spaces before being  
           stored.

            The VARCHAR on the other hand support variable length strings therefore stores
strings smaller than the field size without modification.

    Q3. Explain the following constraints:-
Ans (a) NOT NULL:- ensures that column cannot have null values.
b) CHECK:- make sure that all values satisfy certain criteria.
c) UNIQUE:- ensures that all values in a column are different.

Q4. What is the role of UNIQUE constraint? How is Primary Key constraint different from UNIQUE constraint?
Ans:- The UNIQUE constraint ensures that all values in a column are distinct. In other words, no two rows can hold the same value for a column with unique constraint .

PRIMARY KEY constraint is different from UNIQUE constraint, since attributes with UNIQUE constraint are permitted to be NULL unless they have explicitly been declared NOT NULL, whereas primary Key attributes are required to be NON NULL and UNIQUE.

Q5. What is DEFAULT constraint?
Ans:- The DEFAULT constraint specifies the default value for the field when no value is specified at time of insertion of record.

Q6. When a column’s value is skipped in an INSERT command, which value is inserted in the database?
Ans:- The column that are not listed in INSERT Command will have their default value, if it is defined for them, otherwise, NULL value.

Q7.Define a Transaction.
Ans:- A Transaction is a logical unit of work (LUW) that must succeed or fail to its entirety. A transaction is an atomic operation which cannot be dived into smaller operations.

Q8. What do you mean by committing a transaction?
Ans:- Committing a transaction means all the steps of a transaction are carried out successfully & all data changes are made permanent in the database.

Q9. What does Transaction RollBack indicate?
Ans:-Transaction RollBack means transaction has not been finished completely and hence all data changes made by the transaction in the database, if any are undone & the database returns to the same state as it was before this transaction execution started.

Q10. What are the different properties of transaction maintained by database systems?
Ans:- To ensure the data integrity, the database system maintains the following properties of transactions:-
1) ATOMICITY:- The property ensures that either all operations  of the transaction are reflected properly in the database, or none are. Atomicity ensures either all or none operations of a transaction are carried out.
2) CONSISTENCY:-This property ensures that the database remains in a consistent state before the start of transaction & after the transaction is over(Whether successfully or unsuccessfully)
3) ISOLATION:- Isolation ensures that each executing transaction execution in isolation i.e is unaware of other transactions executing concurrently in the system.
4) DURABILITY:- This property ensures that after the successful completion of a transaction i.e when a transaction commits the changes made by it to the database persist i.e remain in the database irrespective of other system failures.

Q11. What are the two ways in which multiple transactions can be executed?
Ans:- Multiple transactions can be executed in one of the following two ways:-
(i)                 Serially i.e Serial execution of Transactions. It ensures consistency of the database.
(ii)               Concurrently i.e. Concurrent(simultaneous) execution of transactions.

Q12. What is SAVEPOINT?
Ans:- Savepoints are special operations that allow you to divide the work of a transaction into different segments. Incase of a failure, you can execute rollbacks to the savepoint only, leaving prior changes intact. Savepoints are great for situations where part of the transaction needs to be recovered in an uncommitted transaction,

Q13. What are single row and multiple row functions?
Ans:- Single Row Functions:- work with single row at a time. A single row function returns a result for every row of a queried table. Eg- Sqrt(), Concat(), Upper() etc.
Multiple Row Functions:- work with data of multiple rows at a time and return aggregated value. Eg:-  Sum (), Count(), Max(), Min() etc.

Q14.  What is Cartesian Product? How is it related to join?
Ans:- Cartesian Product is a very basic type of join that simply matches each row from one table to every row from another table.(Where the result table contains Addition of columns of two tables and rows would be multiplication of the number of rows in the two tables)

Q15.  What is a join? How  many types of joins can you create in MySQL ?
Ans:- Join is a query that combines rows from one or more tables. Different types of join in MySQL are :-
(i) Equi Join                 (ii) Non Equi Join                    (iii) Natural Join
(iv) Cross Join             (v) Left, Right Join

Q16. Define :-
Ans:-
a) Equi Join:- In an Equi Join the values in the columns being joined are compared for equality. All the columns in the tables being joined are included in the results.
b) Non Equi Join:- A join in which tables are compared for some relationship other than equality.
c) Natural Join:- In Natural join one of the two identical columns can be eliminated by restating the query. i.e. in natural join the result consists of only the matched rows.
d)Cross Join:- all possible concatenations are formed of all rows in both the tables.
e) Left join:- Left Join is a form of join where all the rows (even non matching ones) from the first table are returned into the result.

Q17. Can you have multiple primary keys  and multiple foreign keys in your Table?
Ans:- No we can have only one primary key in out table.
Yes, a table may have multiple foreign keys, and each foreign key can have different referenced table.

Q18. In MySQL, what table type(storage engine) is required for foreign keys to work?
Ans:- InnoDB

Q19. how would you find the storage engine of your table?
Ans:- SHOW CREATE TABLE < table-name>;

Q20. With which command, can you change storage engine of your table?
Ans:- ALTER TABLE <table- name> ENGINE=InnoDB;

Q21. What do you mean by Relational database?
Ans:- The relational database is one that follows the relational data model i.e., it stores information in the form of related tables consisting of rows and columns

Q22. Drop Table Employee2
Ans: DELETE FROM EMPLOYEE2;
         DROP TABLE EMPLOYEE2;

Q23. Change the data type of column pincode to varchar(10) in the table customer.
Ans:- ALTER TABLE CUSTOMER
          MODIFY (Pincode VARCHAR(10));

Q24. ADD a PRIMARY KEY constraint to the table Employee2 using the EmpID column.
Ans:-  ALTER TABLE Employee2
ADD CONSTRAINT P_key
PRIMARY KEY(EmpID);

Q25. Can a foreign key refer to a column that has not been declared to be the primary key of a table? Explain.
Ans:- Yes a foreign Key can also refer to the columns that have been declared to be unique(i.e. the alternate keys of the table).

Q26. What is MySQL. Give its key features.
Ans:- MySQl is  a freely available open source  Relational Database Management System(RDBMS) that uses Structured Query Language(SQL). In a MySQL database, information is stored in Tables. A single MySQL database can contain many tables at once and store thousands of individual records.
Its features include a secure environment for storing, maintaining and accessing data. MySQl is a fast, reliable, scalable alternative to many of the commercial RDBMSs available today.

Q27.  How are SQL Commands classified?
Ans:- SQL commands are classified into following categories:-
(i) Data Definition Language(DDL) Commands:- Commands that allow us to perform tasks released to data definition e.g.
  • Creating, altering  and dropping.(CREATE TABLE, ALTER TABLE etc.)
  • Granting and revoking privileges and roles.
  • Maintenance commands
(ii) Data Manipulation Languages(DML) commands:- Commands that allow us to perform data manipulation e.g. retrieval, insertion, deletion and modification of data stored in a database. e.g insert, select, update , delete etc.
(iii) Transaction Control Commands(TCL):- Commands that allow us to manage and control the transaction( a transaction  is one complete unit of work involving many steps) e.g.
  • Making changes to database permanent
  • Undoing changes to database, permanent
  • Creating Savepoints
  • Setting properties for current transactions

Q28. How do you define a foreign key in your table?
Ans:- a foreign Key is a field (or fields) that point to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words only values that are supposed to appear in the database are permitted.

In create Table command, one can add foreign key’s definition through the following syntax:
Foreign Key(<column-to-be designated as foreign key>) references Master Table(<primary Key of Master table>);


 
CREATE TABLE ORDERS
(
  Order_ID integer,
  Order_Date  date,
  Customer_Sid integer,
  Amount double,
  Primary Key (order_ID),
  Foreign key (customer_SID) references CUSTOMER (SID)
);

Q29. List employee last name, work departments and monthly salary for those in Services, Planning and Information centre Departments.


Ans:- SELECT LastName, WorkDept, Salary
From Empl, Dept
Where WorkDept=Deptno
AND DeptName IN (‘Services’, ‘Planning’, Information Centre’);

Q30. Add one column Email of data type VARCHAR and size  30 to the table Customer.
Ans:- ALTER TABLE CUSTOMER
          ADD (Email VARCHAR(30));

Q31. Give the commands to perform the following:-
(a)    create the table structure Tour (TourNo, busNo, TicketPrice).
              CREATE TABLE Tour
               (
                   TourNo VARCHAR(5),
                   BusNo VARCHAR (15),
                   TicketPrice INT(5)
               );
(b)   Define the combination of TourNo and BusNo the Primary key
ALTER TABLE Tour  ADD CONSTRAINT PK_TOUR PRIMARY KEY (TourNo,BusNo);
     
      (c ) Create  the Table  structure Tourist (TourNo, BusNo, SeatNo, Name) where the combination of TourNo, BusNo and SeatNo is the Primary key
                        CREATE TABLE Tourist
                      (
                          TourNo VARCHAR(5),
                          BusNo VARCHAR(15),
                          SeatNo int(2),
                         Name VARCHAR(20),
                         CONSTRAINT PK_Tourist PRIMARY KEY(TourNo, BusNo, SeatNo)
                       );
     (d)  Define the combination of TourNo and BusNo a foreign key that refers to the                       
corresponding combination of the Tour Table.
              ALTER TABLE Tourist
             ADD CONSTRAINT FK_TOURIST _TOUR FOREIGN KEY(TourNo, BusNo)   
             REFERENCES Tour (TourNo, BusNo);

Q32. Give the command to create the table
            Product(ProdCode, Name, Price, FreeProdCode)
            Where FreeProdCode refers to ProdCode.
Ans:-
           CREATE Table Product
            (  ProdCode CHAR(5) Priamry Key,
               Name VARCHAR(20),
               Price INT(5),
               FreeProdCode CHAR(5) FOREIGN KEY (FreeProdCode)
               RERENCES product (ProductCode)
             );
Q33. Name  and explain the different data models?
Ans:- A data model is a collection of conceptual tools for describing data, data relationships, data semantics etc. There are generally three data models available:-
  • Relational Model:- The relational model represents data and relationships among data by a collection of tables known as relations, each of which has a number of columns with unique names.
  • Network Models:- The network Model represents data by collections of records and relationships among data are represented by links which can be viewed as pointers. The records in the database are organized as collection of arbitrary graphs.
  • Hierarchical Model:- The hierarchical model is similar to the network model in the sense that data and relationships among data are represented by records and links respectively. It differs from the network model in that the records are organized as collections of trees rather than arbitrary graphs. The relational model differs from the network and hierarchical models in that it does not use pointers or links. Instead , the relational model relates records by the values they contain.

Q34. Write SQL Commands for the following on the basis of given table CLUB:-
COACH_ID
COACHNAME
AGE
SPORTS
DATEOFAPP
PAY
SEX
1
KUKREJA
35
KARATE
27/03/1996
1000
M
2
RAVINA
34
KARATE
20/01/1998
1200
F
3
KARAN
34
SQUASH
19/02/1998
2000
M
4
TARUN
33
BASKETBALL
01/01/1998
1500
M
5
ZUBIN
36
SWIMMING
12/01/1998
750
M
6
KETAKI
36
SWIMMING
24/02/1998
800
F
7
ANKITA
39
SQUASH
20/02/1998
2200
F
8
ZAREEN
37
KARATE
22/02/1998
1100
F
9
KUSH
41
SWIMMING
13/01/1998
900
M
10
SHAILYA
37
BASKETBALL
19/02/1998
1700
M

a)      to show all information about the swimming coaches in the club
SELECT * FROM CLUB WHERE SPORTS=’SWIMMING’;
b)      to list names of all coaches with their date of appointment (DATEOFAPP) in descending order.
SELECT COACHNAME, DATEOFAPP FROM CLUBORDER BY DATEOFAPP DESC;
c)      To display a report, showing coachname , pay age and bonus(15% of pay) for all the coaches.
SELECT COACHNAME, PAY,AGE,PAY*0.15 AS BONUS FROM CLUB;
d)     Give the output of following SQL Commands
(i)                 SELECT LCASE(SPORTS) FROM CLUB;
LCASE(SPORTS)
karate
karate
squash
basketball
swimming
swimming
squash
karate
swimming
basketball

(ii)               SELECT MOD(AGE,5) FROM CLUB WHERE SEX=’F’;
MOD(AGE,5)
4
1
4
2

(iii)             SELECT POWER(3,2) FROM CLUB WHERE SPORTS =’KARATE’;
POWER(3,2)
                  9
9         
9

(iv)             SELECT SUBSTR(COACHNAME, 1,2) FROM CLUB WHERE DATEOFAPP>’1998-01-31’;
Substr(coachname,1,2)
KA
KE
AN
ZA
SH

(v)               SELECT COUNT(DISTINCT SPORTS ) FROM CLUB;
4
(vi)             SELECT MIN(AGE) FROM CLUB WHERE SEX=’F’;
34
(vii)           SELECT AVG(PAY) FROM CLUB WHERE SPORTS=’KARATE’;
1100.0000
(viii)         SELECT SUM(PAY) FROM CLUB WHERE SPORTS=’SWIMMING’;
NULL
Q35. Consider the following tables Item and Customer. Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii)
Table: Item
I_ID
ItemName
Manufacturer
Price
PC01
Personal Computer
ABC
35000
LC05
Laptop
ABC
55000
PC03
Personal Computer
XYZ
32000
PC06
Personal Computer
COMP
37000
LC03
Laptop
PQR
57000
Table : Customer
C_ID
CustomerName
City
I_ID
01
N Roy
Delhi
LC03
06
H Singh
Mumbai
PC03
12
R Pandey
Delhi
PC06
15
C Sharma
Delhi
LC03
16
K Agarwal
Banglore
PC01
(i)                 To display the details of those customers whose City is Delhi

SELECT * FROM CUSTOMER
WHERE City = ‘Delhi’;

(ii)               To display the details of Item whose Price is in the range of 35000 to
55000(Both values included)

SELECT * FROM ITEM
WHERE PRICE BETWEEN 35000 TO 55000;

(iii)             To display the CustomerName, City from table Customer, and ItemName and Price from table Item, with their corresponding matching I_ID

SELECT CustomerName, City , ItemName, Price
FROM CUSTOMER, ITEM
WHERE CUSTOMER.I_ID =ITEM.I_ID;

(iv)             To increase the Price of all Items by 1000 in the table Item.

UPDATE ITEM
SET Price = Price + 1000;

(v)                SELECT DISTINCT CITY FROM CUSTOMER;

Delhi
Mumbai
Bangalore

(vi)             SELECT ItemName, Max (Price), Count(*) FROM Item Group By ItemName;

Personal Computer            37000              3
Laptop                               57000              2


(vii)           SELECT CustomerName, Manufacturer FROM Item, Customer WHERE Item.Item_ID = customer.Item_Id;

N Roy                   PQR
H Singh                 XYZ
R Pandey              COMP
C Sharma              PQR
K Agarwal                        ABC



(viii)         SELECT ItemName, Price * 100
FROM  Item WHERE Manufacturer = ‘ABC’;

                             Personal Computer             3500000
                             Laptop                                5500000
Q36.(a) CREATE TABLE Customer as per the following table Instance Chart
Column Name
Cust_IDPrimary key
Cust_Name
Cust_Address1
Cust_Address2
PinCode
Cust_Phone
Datatype
Number
Varchar
Varchar
Varchar
Varcahr
Number
Length
7
30
20
20
6
10

(b) Add one column Emailof datatype Varchar and size 30 to the table customer
(c)    Add one more column CustomerIncome Group from table Customer

a)      Create Table Customer
      (      Cust_ID( int (7) primary Key,
             Cust_Name varchar(30),
             Cust_Address1 varchar(20),
              Cust_Address2 varchar(30),
             PinCode int(6),
            Cust_Phone Varchar(10));

(b) Alter Table Customer
     Add (Email varchar (30));
     
    (c ) Alter Table Customer
   Add (customerIncomeGroup varchar(10));

(d)   Alter Table Customer
Drop CustomerIncomeGroup;

Q37. (a)Write SQL Queries for the following
Column Name
DeptID
DeptName
Key Type
Primary

Null/Unique

Not Null
Datatype
Number
Varcahr
Length
2
20
(b) Create table employee as per the following Table Instance Chart
Column Name
EmpID
EmpName
EmpAddress
EmpPhone
EmpSal
DeptID
Key Type
Primary




Foreign
Null/Unique

Not Null




FK Table





Department
FK Column





DeptID
Datatype
Number
Varchar
Varchar
Varchar
Number
varchar
Length
6
20
30
10
9,2
2

Ans:- (a) Create table Department
                (  DeptID int (2) Primary Key,
                    Deptname varchar(20) NOTNULL);
(b) Create Table employee
     (
        EmpID int(6) Primary Key,
        EmpName varchar(20) Not Null,
        EmpAddress varcahr(30),
        EmpPhone varchar (10),
        EmpSal double(9,2),
        DeptID int(2),
       Foreign Key(DeptID) References Department (DeptID)
) Engine=InnoDB;

Q38. Write the output of the following SQL queries
(i)                 Select ROUND(6.5675,2);
               6.57
(ii)               Select Truncate(5.3456,1);
5.3
(iii)             Select DayofMonth(‘2009-08-25);
              25
(iv)             Select MID(‘Class 12’, 23);

               las