{"id":12986,"date":"2019-12-17T18:05:57","date_gmt":"2019-12-17T12:35:57","guid":{"rendered":"https:\/\/kseebsolutions.guru\/?p=12986"},"modified":"2021-07-02T15:36:28","modified_gmt":"2021-07-02T10:06:28","slug":"2nd-puc-computer-science-question-bank-chapter-14","status":"publish","type":"post","link":"https:\/\/kseebsolutions.guru\/2nd-puc-computer-science-question-bank-chapter-14\/","title":{"rendered":"2nd PUC Computer Science Question Bank Chapter 14 SQL Commands"},"content":{"rendered":"

You can Download Chapter 14 SQL Commands Questions and Answers, Notes, 2nd PUC Computer Science Question Bank with Answers<\/a> Karnataka State Board Solutions help you to revise complete Syllabus and score more marks in your examinations.<\/p>\n

Karnataka 2nd PUC Computer Science Question Bank Chapter 14 SQL Commands<\/h2>\n

2nd PUC Computer Science SQL Commands One Mark Questions and Answers<\/h3>\n

Question 1.
\nExpand SQL.
\nAnswer:
\nThe SQL is expanded as ‘Structured Query Language’.<\/p>\n

Question 2.
\nGive the syntax for create command in SQL.
\nAnswer:
\nSyntax:
\nCREATE TABLE tablename (columnnamel datatype(size), columnname2 datatype(size) …);<\/p>\n

Question 3.
\nWhat is drop command in SQL.
\nAnswer:
\nThe drop command is used to remove\/delete tables,
\nsyntax:
\nDROP TABLE tablename;<\/p>\n

\"KSEEB<\/p>\n

Question 4.
\nGive the command to display all the details in the table.
\nAnswer:
\nTo view All the Columns and all the Rows (Entire Table values)
\n> SELECT * FROM student;<\/p>\n

Question 5.
\nWhat is update command?
\nAnswer:
\nThe update command is used to change row values from a table. The SET keyword takes the column in which values needs to be changed or updated<\/p>\n

Question 6.
\nWhat is commit command?
\nAnswer:
\nThe commit command is used to save the transactions entered into the table.<\/p>\n

2nd PUC Computer Science SQL Commands Two Mark Questions and Answers<\/h3>\n

Question 1.
\nClassify numeric and character string data types in SQL.
\nAnswer:<\/p>\n

    \n
  • Numeric data type is classified as exact numeric data types and floating-point numeric data types.<\/li>\n
  • Character, string data types is classified as char and varchar data types.<\/li>\n<\/ul>\n

    Question 2.
    \nClassify various SQL operators.
    \nAnswer:
    \nThe various SQL operators are Arithmetic operators, Comparison operators, Logical operators, Operators used to negate conditions.<\/p>\n

    Question 3.
    \nWhich are the logical operators in SQL.
    \nAnswer:
    \nThe logical operators in SQL are ALL, AND, ANY BETWEEN, EXISTS, IN, LIKE, NOT, OR, IS NULL, UNIQUE.<\/p>\n

    \"KSEEB<\/p>\n

    Question 4.
    \nHow do you modify the column name and width for existing table?
    \nAnswer:
    \nSyntax:
    \nALTER TABLE tablename MODIFY (columnname datatype (size), columnname datatype(size)..);<\/p>\n

    Question 5.
    \nWrite the syntax for distinct command in SQL.
    \nAnswer:
    \nSyntax:
    \nSELECT DISTINCT columnname FROM tablename;<\/p>\n

    Question 6.
    \nWhat is the use of NULL value?
    \nAnswer:
    \nA field with a value of NULL means that the field actually has no value stored in it.<\/p>\n

    \"KSEEB<\/p>\n

    Question 7.
    \nWhat is create view command?
    \nAnswer:
    \nA view is referred to as a virtual table. Views are created by using the CREATE VIEW statement.<\/p>\n

    Question 8.
    \nWhat is the dual table?
    \nAnswer:
    \nIt is single row and single column dummy table provided by oracle.<\/p>\n

    2nd PUC Computer Science SQL Commands Three Mark Questions and Answers<\/h3>\n

    Question 1.
    \nExplain the features of SQL.
    \nAnswer:
    \nThe features of SQL:<\/p>\n

      \n
    • SQL is ah ANSI and ISO standard computer language for creating and manipulating databases.<\/li>\n
    • SQL allows the user to create, update, delete, and retrieve data from a database.<\/li>\n
    • SQL is very simple and easy to learn.<\/li>\n
    • SQL works with database programs like DB2, Oracle, MS Access, Sybase, MS SQL Server, etc.<\/li>\n
    • SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.<\/li>\n
    • Well, Defined Standards Exist.<\/li>\n<\/ul>\n

      Question 2.
      \nList the components of SQL architecture.
      \nAnswer:
      \nWhen executing an SQL command for any RDBMS, the SQL engine interprets the task for execution. There are various components included in the process. These components of SQL architecture are Query Dispatcher, Optimization Engines, Classic Query Engine, and SQL Query Engine, etc.,<\/p>\n

      Question 3.
      \nExplain DDL commands with example.
      \nAnswer:
      \nDDL -Data Definition Language commands create database objects such as tables, views, etc., The various DDL commands are Create Table, Alter Table, Create View, Drop Table.
      \n-Create Table
      \nSYNTAX:
      \nCREATE TABLE table_name
      \n( field1 datatype [ NOT NULL ],
      \nfield2 datatype [ NOT NULL ],
      \nfield3 datatype [ NOT NULL ]…)
      \nAn example of a CREATE TABLE statement follows.
      \nSQL> CREATE TABLE BILLS (
      \n2 NAME CHAR(30),
      \n3 AMOUNT NUMBER,
      \n4 ACCOUNT_ID NUMBER);<\/p>\n

      The ALTER TABLE command is used to do two things:<\/p>\n

        \n
      • Add a column to an existing table<\/li>\n
      • Modify a column that already exists<\/li>\n<\/ul>\n

        SYNTAX:
        \nALTER TABLE table_name ADD( column_name data_type);
        \nALTER TABLE table_name MODIFY (column_name data_type);
        \nThe following command changes the NAME field of the BILLS table to hold 40 characters:
        \nSQL> ALTER TABLE BILLS MODIFY (NAME CHAR(40));<\/p>\n

        DROP command is used to remove the entire table from the database.
        \nsyntax:
        \nDROP TABLE tablename;
        \nExample:
        \nDROP TABLE student;<\/p>\n

        \"KSEEB<\/p>\n

        Question 4.
        \nExplain DML commands with example.
        \nAnswer:
        \nThe data manipulation commands are used for retrieval (view) of data, insertion of new data, modification of data or deletion. The DML commands includes insert, delete and update.
        \n1. INSERT command:
        \nIt is used to inserts new rows into the table.
        \nSyntax:
        \nINSERT INTO tablename (columnname1, columnname2, … ) VALUES ( value1, value2, ..);
        \nExample:
        \nINSERT INTO student (regno, name, Combn , fees ) VALUES (1234, ‘Hemanth’, ‘PCMCs’, 15000);<\/p>\n

        2. UPDATE command:
        \nIt can be used to change row values from a table. The SET key word takes the column in which values needs to be changed or updated. The WHERE keyword is used to filter the records on some condition.
        \nSyntax:
        \nUPDATE tablename SET columnname = values WHERE Condition;
        \nExample:
        \nUPDATE student SET combn = ‘PCMCs’ where combn=’pcmc’;<\/p>\n

        3. DELETE Command:
        \nIt is used to delete\/remove the tuples\/rows from the table. All the rows will be deleted if WHERE clause is not used in the statement otherwise it selects the rows for delete which satisfies the condition.
        \nSyntax:
        \nDELETE from tablename WHERE Condition;
        \nExample:
        \nDELETE from student;
        \nDELETE from student WHERE regno=1234;<\/p>\n

        Question 5.
        \nExplain with an example boolean expression in SQL.
        \nAnswer:
        \nBoolean expressions return rows (results) when a single value is matched. Boolean expressions commonly used in a WHERE clause are made of operands operated on by SQL operators.
        \nFor example,
        \n> SELECT * FROM EMPLOYEES WHERE AGE = 45;
        \nThe above statement returns all those records (rows) whose age column is having the exact value of 45 from the employees table.<\/p>\n

        Question 6.
        \nExplain AND operator using where in SQL.
        \nAND operator:
        \nAnswer:
        \nThe operator AND means that the expressions on both sides must be true to return TRUE. If either expression is false AND returns FALSE.
        \nSyntax:
        \nSELECT column1, column2, columnN FROM table_name
        \nWHERE condition1 AND condition2 … AND conditionN;<\/p>\n

        Example 1:
        \nIt would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 2000 AND age is less than 25 years:
        \nSQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;<\/p>\n

        Example 2:
        \nTo find out which employees have been with the company for 5 years or less and have taken more than 20 days leave,:
        \nSQL> SELECT empNAME FROM VACATION WHERE YEARS <= 5 AND LEAVETAKEN > 20;<\/p>\n

        Question 7.
        \nList the built-in functions associated with Group functions in SQL.
        \nAnswer:
        \nThe built-in functions associated with GROUP functions in SQL are
        \n1. COUNT function- returns the count of records that satisfies the condition for each group of records.
        \nExample:
        \nSELECT department, COUNT(*)FROM employees WHERE salary > 25000 GROUP BY department;<\/p>\n

        2. MAX function- returns the maximum values from the column for each group of records.
        \nExample:
        \nSELECT department, MAX(salary) FROM employees GROUP BY department;<\/p>\n

        3. MIN function – returns the lowest values from the column for each group of records.
        \nExample:
        \nSELECT department, MIN(salary)FROM employees GROUP BY department;<\/p>\n

        4. AVG function – returns the average values from the column for each group of records.
        \nExample:
        \nSELECT AVG(cost) FROM products WHERE category = ‘Clothing’;<\/p>\n

        5. SUM function- returns the total values from the column for each group of records.
        \nExample:
        \nSELECT department, SUM(sales)FROM order_details GROUP BY department;<\/p>\n

        6. DISTINCT function – returns the once occurrence of many repeated values from the column for each group of records.
        \nExample:
        \nSELECT AVG(DISTINCT cost)FROM products WHERE category = ‘Clothing’;<\/p>\n

        \"KSEEB<\/p>\n

        Question 8.
        \nWhat is the use of JOIN command?
        \nAnswer:
        \nSQL joins are used to combine rows from two or more tables.
        \nThere are 4 different types of SQL joins:<\/p>\n

          \n
        • SQL INNER JOIN (or sometimes called simple join)<\/li>\n
        • SQL LEFT JOIN<\/li>\n
        • SQL RIGHT JOIN<\/li>\n
        • SQL FULL JOIN<\/li>\n<\/ul>\n

          1. SQL INNER JOIN (simple join)
          \nSQL INNER JOINS return all rows from multiple tables where the join condition is met.
          \nSyntax:
          \n> SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
          \nIn this visual diagram, the SQL INNER JOIN returns the shaded area:
          \n\"2nd
          \nThe SQL INNER JOIN would return the records where table1 and table2 intersect.<\/p>\n

          2. SQL LEFT JOIN:
          \nThis type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condi\u00action is met).
          \nSyntax:
          \n> SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
          \nIn this visual diagram, the SQL LEFT JOIN returns the shaded area:
          \n\"2nd
          \nThe SQL LEFT JOIN would return the all records from table1 and only those records from table2 that intersect with table1.<\/p>\n

          3. SQL RIGHT JOIN:
          \nThis type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
          \nSyntax:
          \n> SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
          \nIn this visual diagram, the SQL RIGHT JOIN returns the shaded area:<\/p>\n

          \"2nd<\/p>\n

          The SQL RIGHT JOIN would return the all records from table2 and only those records from table1 that intersect with table2.<\/p>\n

          4. SQL FULL JOIN:
          \nThis type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.
          \nSyntax:
          \n> SELECT columns FROM table1 FULL JOIN table2 ON table1.column = table2.column;
          \nIn this visual diagram, the SQL FULL JOIN returns the shaded area:
          \n\"2nd<\/p>\n

          The SQL FULL JOIN would return the all records from both table1 and table2.<\/p>\n

          Question 9.
          \nWhat are privileges and roles?
          \nAnswer:
          \nThe Privileges defines the access rights given to a user on a database object. There are two types of privileges.<\/p>\n

            \n
          • System privileges – This allows the user to CREATE, ALTER, or DROP database objects.<\/li>\n
          • Object privileges – This allows the user to EXECUTE, SELECT, INSERT, UPDATE, or DELETE data from database objects to which the privileges apply.<\/li>\n<\/ul>\n

            Few CREATE system privileges are listed below:<\/p>\n

            CREATE object – allows users to create the specified object in their own schema.
            \nCREATE ANY object – allows users to create the specified object in any schema.<\/p>\n

            Few of the object privileges are listed below:<\/p>\n

              \n
            • INSERT – allows users to insert rows into a table.<\/li>\n
            • SELECT – allows users to select data from a database object.<\/li>\n
            • UPDATE – allows user to update data in a table.<\/li>\n
            • EXECUTE – allows user to execute a stored procedure or a function.<\/li>\n<\/ul>\n

              Roles:
              \nRoles are a collection of privileges or access rights. When there are many users in a database it becomes difficult to grant or revoke privileges to users. Therefore, if roles are defined, one can grant or revoke privileges to users, thereby automatically granting or revoking privileges.<\/p>\n

              Some of the privileges granted to the system roles are as given below:<\/p>\n

              1. CONNECT – CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE SESSION, etc.<\/p>\n

              2. RESOURCE – CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIG-GER, etc.<\/p>\n

              The primary usage of the RESOURCE role is to restrict access to database objects. DBA- ALL SYSTEM PRIVILEGES.<\/p>\n

              Question 10.
              \nClassify various built-in functions in SQL.
              \nAnswer:
              \nThe built-in functions are classified as Single row functions and Group functions.
              \nThe single-row functions are of four types. They are numeric functions, character or text functions, date functions, and conversion functions.<\/p>\n

              1. Numeric functions:
              \nABS function- The ABS function returns the absolute value of the number you point to.<\/p>\n

              a. CEIL and FLOOR functions – CEIL returns the smallest integer greater than or equal to its argument. FLOOR does just the reverse, returning the largest integer equal to or less than its argument.<\/p>\n

              b. POWER function – To raise one number to the power of another, use POWER. In this function the first argument is raised to the power of the second:<\/p>\n

              c. SQRT function – The function SORT returns the square root of an argument.<\/p>\n

              2. Character or text functions:
              \na. CHR function – returns the character equivalent of the number it uses as an argument.<\/p>\n

              b. CONCAT function – function combines two strings together.<\/p>\n

              c. INITCAP function – capitalizes the first letter of a word and makes all other characters lowercase.<\/p>\n

              d. LOWER and UPPER functions – LOWER changes all the characters to lowercase; UPPER does just the reverse.<\/p>\n

              e. LENGTH function – returns the length of its lone character argument.<\/p>\n

              3. Date functions:
              \na. ADD_MONTHS function – This function adds a number of months to a specified date.<\/p>\n

              b. LAST_DAY – LAST_DAY function- returns the last day of a specified month. MONTHS_BETWEEN function – to know how many months fall between month x and month y,<\/p>\n

              c. NEXT_DAY function – finds the name of the first day of the week that is equal to or later than another specified date.<\/p>\n

              d. SYSDATE function – SYS DATE returns the system time and date.<\/p>\n

              4. Conversion functions:
              \na. TO_CHAR function – to convert a number into a character.<\/p>\n

              b. TO_NUMBER function – it converts a string into a number.<\/p>\n

              The group functions are:<\/p>\n

                \n
              • AVG() – Returns the average value<\/li>\n
              • COUNT() – Returns the number of rows<\/li>\n
              • FIRST() – Returns the first value<\/li>\n
              • LAST() – Returns the last value<\/li>\n
              • MAX() – Returns the largest value<\/li>\n
              • MIN() – Returns the smallest value<\/li>\n
              • SUM() – Returns the total value of the given column.<\/li>\n<\/ul>\n

                2nd PUC Computer Science SQL Commands Five Mark Questions and Answers<\/h3>\n

                Question 1.
                \nExplain SQL constraints with example.
                \nAnswer:
                \nSQL Constraints are rules used to limit the type of data that can be stored into a table, to maintain the accuracy and integrity of the data inside table.
                \nConstraints can be divided into two types,<\/p>\n

                  \n
                • Column level constraints: limits only column data<\/li>\n
                • Table level constraints: limits whole table data<\/li>\n<\/ul>\n

                  Constraints are used to make sure that the integrity of data is maintained in the database. The NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT are the most used constraints that can be applied to a table.<\/p>\n

                  1. NOT NULL Constraint:
                  \nNOT NULL constraint restricts a column from having a NULL value. Once *NOT NULL* constraint is applied to a column, you cannot store null value to that column. It enforces a column to contain a proper value.
                  \nExample using NOT NULL constraint
                  \nCREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);<\/p>\n

                  2. UNIQUE Constraint:
                  \nUNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE constraint field will not have duplicate data.
                  \nExample using UNIQUE constraint when creating a Table
                  \nCREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);<\/p>\n

                  3. Primary Key Constraint:
                  \nThe primary key constraint uniquely identifies each record in a database. A Primary Key must contain unique value and it must not contain a null value.
                  \nExample using PRIMARY KEY constraint
                  \nCREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);<\/p>\n

                  4. Foreign Key Constraint:
                  \nFOREIGN KEY is used to relate two tables. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
                  \nExample using FOREIGN KEY constraint at Table Level
                  \nCREATE TABLE Orders
                  \n(
                  \nO-Id int NOT NULL PRIMARY KEY,
                  \nOrderNo int NOT NULL,
                  \nP_Id int FOREIGN KEY REFERENCES Persons(P_Id)
                  \n)<\/p>\n

                  5. CHECK Constraint:
                  \nCHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database.
                  \nExample using CHECK constraint
                  \ncreate table Student(s_id int NOT NULL CHECK(s_id > 0), Name varchar(60) NOT NULL, Age int);<\/p>\n

                  \"KSEEB<\/p>\n

                  Question 2.
                  \nExplain with example to create details of employees and give the minimum and maximum in the salary domain.
                  \nAnswer:
                  \nThe CHECK constraint is used to limit the value range that can be placed in a column.
                  \nIf you define a CHECK constraint on a single column it allows only certain values for this column.<\/p>\n

                  Check Constraint at column level:
                  \nCREATE TABLE employee
                  \n( id number(5) PRIMARY KEY,
                  \nname char(20),
                  \ndept char(10),
                  \nage number(2),
                  \ngender char(l),
                  \nsalary number(lO) CHECK (salary >=5000 AND salary <=40000),
                  \nlocation char(10)
                  \ns);
                  \nIn the above example, the salary column is defined as a number column with a check constraint. It checks constraint checks the value that can be stored in the salary column should be greater than or equal to 5000 which is the minimum value and the value is less than or equal to 40000 which is the maximum value for the column salary.<\/p>\n

                  Question 3.
                  \nWrite the differences between order by and group by with example.
                  \nAnswer:
                  \n1. SQL ORDER BY Clause:
                  \nThe SQL ORDER BY Clause is used in a SELECT statement to sort results either in ascending or descending order.<\/p>\n

                  Syntax for using SQL ORDER BY clause to sort data is:
                  \n>SELECTcolumn-list FROM table_name ORDER BY column1, column2,.. columnN [DESC]];
                  \nFor Example:
                  \nIf you want to sort the employee table by salary of the employee, the SQL query would be.
                  \n> SELECT name, salary FROM employee ORDER BY salary;
                  \nBy default, the ORDER BY Clause sorts data in ascending order. If data to be sorted in descending order, the command would be as given below.
                  \n> SELECT name, salary FROM employee ORDER BY name, salary DESC;<\/p>\n

                  2. SQL GROUP BY Clause:
                  \nThe SQL GROUP BY Clause is used with the group functions to retrieve data grouped according to one or more columns.<\/p>\n

                  The basic syntax of GROUP BY clause is given below. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.
                  \nSELECT column1, column2 FROM table name WHERE [ conditions ]
                  \nGROUP BY column1, column2
                  \nORDER BY column1, column2
                  \nFor Example:
                  \nIf you want to know the total amount of salary spent on each department, the query would be:
                  \n> SELECT dept, SUM (salary) FROM employee GROUP BY dept;
                  \nThe group by clause should contain all the columns in the select list expect those used along with the group functions.
                  \n> SELECT location, dept, SUM (salary) FROM employee GROUP BY location, dept;<\/p>\n","protected":false},"excerpt":{"rendered":"

                  You can Download Chapter 14 SQL Commands Questions and Answers, Notes, 2nd PUC Computer Science Question Bank with Answers Karnataka State Board Solutions help you to revise complete Syllabus and score more marks in your examinations. Karnataka 2nd PUC Computer Science Question Bank Chapter 14 SQL Commands 2nd PUC Computer Science SQL Commands One Mark …<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[82],"tags":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/posts\/12986"}],"collection":[{"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/comments?post=12986"}],"version-history":[{"count":0,"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/posts\/12986\/revisions"}],"wp:attachment":[{"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/media?parent=12986"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/categories?post=12986"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kseebsolutions.guru\/wp-json\/wp\/v2\/tags?post=12986"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}