Important Theory Questions - Must Read SQL 1, Degree - It refers to the number of columns in a relation Cardinality - It refers to the number of rows in a relation 2. Domain - A domain is a set of values that can be assigned to an attribute 3. Keys in a Relational Database: (i) Primary Key It is an attribute or set of attributes that uniquely identifies a tuple in a relation. (ii) Candidate Key: The attributes of a table which are eligible to be set as primary key are called as candidate key. (iii) Alternate Key: From the list of candidate key one is chosen as primary key and the remaining are called Alternate key. (iv) Foreign Key: = A Foreign Key is a field (or collection of fields) in one table that uniquely identifies a row of another table. * It is used to establish a link between the data in two tables Candidate Key ‘Alternate Key Unique Key 4. Constraints: Constraints are certain types of restrictions on the data values that an attribute can have. Constraint pel eetelstoe NOT NULL Ensures that a column cannot have NULL values where NULL means missing/ unknown /not applicable value. UNIQUE Ensures that all the values in a column are distinct/unique. DEFAULT A default value specified for the column if no value is provided. PRIMARY KEY The column which can uniquely identify each row or record in a table. FOREIGN KEY The column which refers to value of an attribute defined as primary key in another table. © seaeed with OK Scanner5. Difference between Char and Varchar Data type: char varchar Used for fixed-length character Used for variable-length character strings. strings. It can store maximum of 255 It can store maximum of 65,535 characters characters It pads unused space with blanks | It holds only the characters assigned to it. Memory allocation is static ‘Memory allocation is dynamic Faster than VARCHAR due to fixed | Slower than CHAR due to variable size. length 6. SQL Commands: (i) DDL(Data Definition Language): > create > alter > drop > truncate (ii) DML(Data Manipulatrion Language): > insert > update > delete 7. Difference Primary Key and Unique Key Primary Key Unique Key Identifies an individual tuple uniquely in a relation Uniquely identifies each tuple (row) ina table. [or table, Only one primary key per table. Multiple unique keys can exist for a table, Does allow NULL values for the column. Allows NULL value for the column. Keyword used to assign constraint : primary key | Keyword used to assign constraint : unique 8: Characteristics of Primary key: + Aprimary key uniquely identifies each row (record) within a table. + It does not allow duplicate entries in a table. + The primary key attribute(s) cannot contain mull values. + There can be only one primary key attribute in a table. 9. where clause: The where clause is used to filter the records of a table based on the specified condition 10 . group by: GROUP BY statement is used to group rows based on values in one or more columns. It's particularly useful when you want to calculate aggregate values (such as count, average, sum, or maximum) for each group. © seaeed with OK Scanner11. Having Clause: + The HAVING clause in SQL is used to filter query results based on aggregate functions and groupings, «The HAVING clause allows you to filter groups of data after using the GROUP BY clause. 12. Order by: * The ORDER BY clause is used to sort the result set in either ascending or descending order based on one or more columns. + By default, the ORDER BY command sorts the result set in ascending order. + Ifyou want to sort the records in descending order, you can use the DESCkeyword. SELECT * FROM Customers ORDER BY CustomerName DES\ 13. Difference where and having ‘WHERE Clause HAVING Clause Used to filter rows of a table. ‘User to filter the grouped data. It can be used without GROUP BY clause. ‘To be used with GROUP BY clause. Operates on individual records. (Operates on grouped records. Applied before GROUP BY clause. Applied after GROUP BY clause. Cannot contain aggregate functions. ‘Can contain aggregate functions (e.g., SUM, COUNT). Used with SELECT, UPDATE, and DELETE statements. ‘Used only in SELECT statements. 14. Difference delete and ie Data Manipulation Language (DML) command. Data Definition Language (DDL) command. Used to remove records from a table. Used to (i) remove a table from a database (ii) remove a column or constraint from a table (iii) remove/delete a database Space occupied by the table in memory is not freed even if all tuples are deleted. Frees the table space from memory. Syntax DELETE FROM table_name WHERE condition; Syntax - To drop a table: DROP TABLE table_name; - To drop a database: DROP DATABASE database_name: Eg: To remove a record with empid = 1001 from employee table delete from employee where empid = 1001; Eg:To remove/delete the employee table: DROP TABLE employee; Actions can be rolled back. Actions cannot be rolled back. © seaeed with OK Scanner15. Difference Group by and order by: GROUP BY ORDER BY Itis used to groups rows based on the same value in specified columns. It is used sorts the result set in ascending or descending order based on one or more columns. Often used with aggregate functions (e.g., COUNT, SUM, AVG) to compute statistics for groups. Used to arrange the output rows. Requires an aggregate function (e.g., COUNT, SUM) when used. ‘Columns in the SELECT clause must be either part of the grouping columns or aggregate functions. ‘Does not require an aggregate function. ‘Can select any columns in the SELECT clause. SELECT columni, aggregate_function(column2) FROM table GROUP BY column1; SELECT column1, column2 FROM table ORDER BY column ASG; or SELECT column1, column2 FROM table ORDER BY column! DESC; ‘SQL Commands fi) Creating database: Syntax: Create database databaseName; Create database Class11b23; ) Selecting database: Syntax: Use databaseName; Use Class11b23; (iii) To view the available databases: Show databases; (iv) To create a table: Syntax: Create table tableName(col1Name datatype [constraint], Col2Name datatype [constraint], CoIN_Name datatype [constraint]); Student [Field | DataType | Constraint Roll int imary key. Name Varchar(20) not null ‘Age int DOB date Marks int default 20 Create table Student(Roll integer primary key, Name varchar(25) not null, Age integer, DOB date, Marks integer default 20); (v) To view table structure: Syntax: desc tableName; Eg: desc student; (or) describe tableName; (or) describe student; © seaeed with OK Scanner(vi) Inserting records into the table: Syntax: Insert into tableName values(col1 Value, col2Value,..... ColNValue); Eg. Insert into student values(1001, “Hari”, 14, “2009-05-22”, 80); (vii: Update a record: Syntax: Update tableName set colName = newValue where condition; Eg: ‘update student set marks = 94 where roll = 1002; (viii): Delete a record: Syntax: Delete from tableName where condition; Delete from student where roll = 1004; (ix| Add Column: ‘Syntax: Alter table tableName add colName datatype constraint; Eg: ‘Alter table student add class varchar(5); (x) Add Primary to existing table: ‘Syntax: ‘Alter table tableName add PRIMARY KEY (ColName); Eg: ‘Alter table student add primary key (roll); i) Remove Primary Key: Syntax: Alter table tableName drop Primary key; ‘Alter table student drop Primary key; (xii) Remove column: ‘Syntax: Alter table TableName drop colName; Eg: Alter table Student drop class; (xiii) Remove/delete table: ‘Syntax: Drop table tableName; Eg: Drop table Student; (xiv) View the available tables: show tables; (xv): Viewing the table, ing Table: Syntax: Select * from tableName where condition group by columnName having groupConditon order by columnName [asc| dsc]; Select que: © seamed vith one Seamer