"use strict"; const {Fragment: _Fragment, jsx: _jsx, jsxs: _jsxs} = arguments[0]; const {useMDXComponents: _provideComponents} = arguments[0]; function _createMdxContent(props) { const _components = { code: "code", h3: "h3", li: "li", ol: "ol", p: "p", pre: "pre", strong: "strong", ul: "ul", ..._provideComponents(), ...props.components }, {Definition, English, Explanation, Hinglish, ImportantQA, MCQ, SubTopicHeader, Summary} = _components; if (!Definition) _missingMdxReference("Definition", true); if (!English) _missingMdxReference("English", true); if (!Explanation) _missingMdxReference("Explanation", true); if (!Hinglish) _missingMdxReference("Hinglish", true); if (!ImportantQA) _missingMdxReference("ImportantQA", true); if (!MCQ) _missingMdxReference("MCQ", true); if (!SubTopicHeader) _missingMdxReference("SubTopicHeader", true); if (!Summary) _missingMdxReference("Summary", true); return _jsxs(_Fragment, { children: [_jsx(SubTopicHeader, { id: "3.1", title: "Basic SQL Queries & Functions" }), "\n", _jsxs(Definition, { term: "Structured Query Language (SQL)", children: [_jsx(English, { children: _jsx(_components.p, { children: "Structured Query Language (SQL) is a standardized declarative programming language used to manage relational databases, perform operations on data, and define schema structures." }) }), _jsx(Hinglish, { children: _jsx(_components.p, { children: "SQL (Structured Query Language) ek standardized database language hai jiska use relational databases ko manage karne, data operations perform karne, aur database schemas ko define karne ke liye kiya jata hai." }) })] }), "\n", _jsxs(Explanation, { children: [_jsxs(English, { children: [_jsx(_components.h3, { children: "Data Definition Language (DDL) Commands" }), _jsx(_components.p, { children: "DDL commands manage the structure of database objects (tables, indexes, views):" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "CREATE TABLE:" }), " Defines a new table with columns, data types, and constraints.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "CREATE TABLE Student (\n RollNo INT PRIMARY KEY,\n Name VARCHAR(50) NOT NULL,\n Age INT CHECK (Age >= 17),\n BranchCode VARCHAR(5)\n);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "ALTER TABLE:" }), " Modifies an existing table structure (adding, dropping, or modifying columns).", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "ALTER TABLE Student ADD Email VARCHAR(100);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "DROP TABLE:" }), " Permanently deletes a table and its structure from the database.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DROP TABLE Student;\n" }) }), "\n"] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Data Definition Language (DDL) Commands" }), _jsx(_components.p, { children: "DDL commands database ke tables aur objects ke structure ko manage karti hain:" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "CREATE TABLE:" }), " Ek naya table design karti hai columns aur constraints ke saath.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "CREATE TABLE Student (\n RollNo INT PRIMARY KEY,\n Name VARCHAR(50) NOT NULL,\n Age INT CHECK (Age >= 17),\n BranchCode VARCHAR(5)\n);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "ALTER TABLE:" }), " Pehle se bani table ke structure ko modify karti hai (new column add/drop/modify karna).", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "ALTER TABLE Student ADD Email VARCHAR(100);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "DROP TABLE:" }), " Table aur uske structure ko database se hamesha ke liye delete kar deti hai.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DROP TABLE Student;\n" }) }), "\n"] }), "\n"] })] }), _jsxs(English, { children: [_jsx(_components.h3, { children: "Data Manipulation Language (DML) Commands" }), _jsx(_components.p, { children: "DML commands manage data within the tables:" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "INSERT:" }), " Adds new rows to a table.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "INSERT INTO Student (RollNo, Name, Age, BranchCode) \nVALUES (101, 'Rahul Sharma', 19, 'CO');\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "SELECT:" }), " Queries and retrieves data from tables."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "UPDATE:" }), " Modifies existing data rows.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "UPDATE Student SET Age = 20 WHERE RollNo = 101;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "DELETE:" }), " Removes rows from a table based on a condition.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DELETE FROM Student WHERE RollNo = 101;\n" }) }), "\n"] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Data Manipulation Language (DML) Commands" }), _jsx(_components.p, { children: "DML commands table ke andar ke records/data ko manage karti hain:" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "INSERT:" }), " Table mein naya record add karti hai.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "INSERT INTO Student (RollNo, Name, Age, BranchCode) \nVALUES (101, 'Rahul Sharma', 19, 'CO');\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "SELECT:" }), " Table se data fetch aur search karti hai."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "UPDATE:" }), " Existing data values ko change karti hai.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "UPDATE Student SET Age = 20 WHERE RollNo = 101;\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "DELETE:" }), " Kisi condition ke basis par rows ko remove karti hai.", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DELETE FROM Student WHERE RollNo = 101;\n" }) }), "\n"] }), "\n"] })] }), _jsxs(English, { children: [_jsx(_components.h3, { children: "Built-in SQL Functions" }), _jsx(_components.p, { children: "SQL provides powerful built-in functions:" }), _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Aggregate Functions:" }), " Summarize data across multiple rows:", "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "COUNT()" }), ": Returns the number of rows."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "SUM()" }), ": Returns the total sum of a numeric column."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "AVG()" }), ": Returns the average value."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "MIN()" }), " and ", _jsx(_components.code, { children: "MAX()" }), ": Return the smallest and largest values."] }), "\n"] }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Scalar Functions:" }), " Operate on a single value:", "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: ["String functions: ", _jsx(_components.code, { children: "UPPER()" }), ", ", _jsx(_components.code, { children: "LOWER()" }), ", ", _jsx(_components.code, { children: "LENGTH()" }), ", ", _jsx(_components.code, { children: "SUBSTR()" }), "."] }), "\n", _jsxs(_components.li, { children: ["Date functions: ", _jsx(_components.code, { children: "SYSDATE" }), ", ", _jsx(_components.code, { children: "ADD_MONTHS()" }), ", ", _jsx(_components.code, { children: "CURRENT_DATE" }), "."] }), "\n"] }), "\n"] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Built-in SQL Functions" }), _jsx(_components.p, { children: "SQL database queries mein default functions provide karta hai:" }), _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Aggregate Functions:" }), " Jo multiple rows ke data ko analyze karke single value dete hain:", "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "COUNT()" }), ": Rows ki ginti karta hai."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "SUM()" }), ": Numeric column ke values ka sum karta hai."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "AVG()" }), ": Average value nikalta hai."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.code, { children: "MIN()" }), " aur ", _jsx(_components.code, { children: "MAX()" }), ": Minimum aur maximum value nikalte hain."] }), "\n"] }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Scalar Functions:" }), " Jo single inputs par run hote hain:", "\n", _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: ["String functions: ", _jsx(_components.code, { children: "UPPER()" }), ", ", _jsx(_components.code, { children: "LOWER()" }), ", ", _jsx(_components.code, { children: "LENGTH()" }), "."] }), "\n", _jsxs(_components.li, { children: ["Date functions: ", _jsx(_components.code, { children: "SYSDATE" }), " (current system time)."] }), "\n"] }), "\n"] }), "\n"] })] }), _jsxs(English, { children: [_jsx(_components.h3, { children: "Group By, Having, and Order By" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "GROUP BY:" }), " Groups rows that have the same values into summary rows (typically used with aggregate functions)."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "HAVING:" }), " Enforces a search condition on groups created by the ", _jsx(_components.code, { children: "GROUP BY" }), " clause. (Note: ", _jsx(_components.code, { children: "WHERE" }), " filters rows, ", _jsx(_components.code, { children: "HAVING" }), " filters groups)."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "ORDER BY:" }), " Sorts the query results in ascending (", _jsx(_components.code, { children: "ASC" }), ", default) or descending (", _jsx(_components.code, { children: "DESC" }), ") order."] }), "\n"] }), _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "SELECT BranchCode, COUNT(*), AVG(Age)\nFROM Student\nWHERE Age >= 18\nGROUP BY BranchCode\nHAVING COUNT(*) >= 2\nORDER BY AVG(Age) DESC;\n" }) })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Group By, Having, aur Order By" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "GROUP BY:" }), " Same values wale records ko groups mein divide karta hai (usually aggregate functions ke saath)."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "HAVING:" }), " ", _jsx(_components.code, { children: "GROUP BY" }), " se bane groups par condition lagane ke liye use hota hai. (Note: ", _jsx(_components.code, { children: "WHERE" }), " rows ko filter karta hai, ", _jsx(_components.code, { children: "HAVING" }), " groups ko)."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "ORDER BY:" }), " Query ke output ko ascending (", _jsx(_components.code, { children: "ASC" }), ") ya descending (", _jsx(_components.code, { children: "DESC" }), ") order mein sort karta hai."] }), "\n"] }), _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "SELECT BranchCode, COUNT(*), AVG(Age)\nFROM Student\nWHERE Age >= 18\nGROUP BY BranchCode\nHAVING COUNT(*) >= 2\nORDER BY AVG(Age) DESC;\n" }) })] })] }), "\n", _jsxs(Summary, { children: [_jsx(English, { children: _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "DDL commands (CREATE, ALTER, DROP) manage schema structure; DML (INSERT, SELECT, UPDATE, DELETE) manage records." }), "\n", _jsx(_components.li, { children: "Aggregate functions summarize column data (COUNT, SUM, AVG, MIN, MAX)." }), "\n", _jsx(_components.li, { children: "GROUP BY groups identical data; HAVING filters aggregate groups; ORDER BY sorts records." }), "\n"] }) }), _jsx(Hinglish, { children: _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "DDL commands (CREATE, ALTER, DROP) schema structure badalti hain; DML (INSERT, SELECT, UPDATE, DELETE) data rows badalti hain." }), "\n", _jsx(_components.li, { children: "Aggregate functions multiple rows se summary return karte hain (COUNT, SUM, AVG, MIN, MAX)." }), "\n", _jsx(_components.li, { children: "GROUP BY identical values ko group karta hai; HAVING groups par filter lagata hai; ORDER BY output ko sort karta hai." }), "\n"] }) })] }), "\n", _jsx(ImportantQA, { questions: [{ "q": "Explain the difference between WHERE and HAVING clauses in SQL with an example query.", "marks": 4, "a": { "components": [{ "type": "Explanation", "content": "1. **WHERE Clause:** Used to filter individual rows before they are grouped. It cannot contain aggregate functions. \n2. **HAVING Clause:** Used to filter groups of records created by the GROUP BY clause. It can contain aggregate functions.\n\n*Example Query:*\n```sql\nSELECT DeptId, COUNT(*)\nFROM Employee\nWHERE Salary > 30000 -- filters rows\nGROUP BY DeptId\nHAVING COUNT(*) > 5; -- filters groups\n```1. **WHERE Clause:** Grouping hone se pehle individual rows ko filter karne ke liye use hota hai. Ismein aggregate functions use nahi ho sakte.\n2. **HAVING Clause:** GROUP BY clause se banaye gaye groups par filter lagane ke liye use hota hai. Ismein aggregate functions use ho sakte hain.\n\n*Example Query:*\n```sql\nSELECT DeptId, COUNT(*)\nFROM Employee\nWHERE Salary > 30000 -- rows filter karta hai\nGROUP BY DeptId\nHAVING COUNT(*) > 5; -- groups filter karta hai\n```" }] } }, { "q": "Write SQL queries for the following queries on Table 'Employee(EmpId, Name, Salary, Dept)':\n1. Display details of employees whose salary is between 25000 and 50000.\n2. Find the total salary and average salary of employees in 'IT' department.\n3. Delete employees whose salary is less than 15000.", "marks": 6, "a": { "components": [{ "type": "Explanation", "content": "1. Query 1:\n```sql\nSELECT * FROM Employee WHERE Salary BETWEEN 25000 AND 50000;\n```\n2. Query 2:\n```sql\nSELECT SUM(Salary) AS TotalSalary, AVG(Salary) AS AvgSalary \nFROM Employee WHERE Dept = 'IT';\n```\n3. Query 3:\n```sql\nDELETE FROM Employee WHERE Salary < 15000;\n```1. Query 1:\n```sql\nSELECT * FROM Employee WHERE Salary BETWEEN 25000 AND 50000;\n```\n2. Query 2:\n```sql\nSELECT SUM(Salary) AS TotalSalary, AVG(Salary) AS AvgSalary \nFROM Employee WHERE Dept = 'IT';\n```\n3. Query 3:\n```sql\nDELETE FROM Employee WHERE Salary < 15000;\n```" }] } }] }), "\n", _jsx(MCQ, { questions: [{ "id": "2a0e15c6-4689-4277-aa32-379d15f6aa09", "q": "Which of the following is a DML command?", "options": ["CREATE", "ALTER", "UPDATE", "DROP"], "answer": 2, "hint": "Think of a command that alters existing data values inside rows.", "explanation": "UPDATE is a Data Manipulation Language (DML) command since it modifies row data." }, { "id": "b28e4a32-9ded-47bc-8010-0617f4d1362e", "q": "Which aggregate function returns the total count of rows matching a condition?", "options": ["SUM()", "COUNT()", "TOTAL()", "ADD()"], "answer": 1, "hint": "It counts the items.", "explanation": "COUNT() is the built-in aggregate function that counts rows." }, { "id": "a34849d7-b84b-43c8-a667-d324740c0bab", "q": "In which order does SQL sort query results by default when using ORDER BY?", "options": ["Descending", "Ascending", "Alphabetical only", "Random order"], "answer": 1, "hint": "Default sorting is lowest to highest.", "explanation": "By default, the ORDER BY clause sorts output rows in Ascending (ASC) order." }, { "id": "0eb96468-0567-4b6e-82b6-db28827a2121", "q": "Which SQL command is used to add a new column to an existing table structure?", "options": ["ADD COLUMN", "ALTER TABLE", "UPDATE TABLE", "INSERT TABLE"], "answer": 1, "hint": "It is a structure-changing DDL command.", "explanation": "ALTER TABLE is used with ADD clause to add a new attribute to an existing table schema." }, { "id": "f83210dd-e210-4e19-a8cf-347e57947127", "q": "What is the correct SQL statement to delete a table and all its data permanently from a database schema?", "options": ["REMOVE TABLE table_name;", "DELETE TABLE table_name;", "DROP TABLE table_name;", "TRUNCATE TABLE table_name;"], "answer": 2, "hint": "Drop is a DDL operation that completely removes the table.", "explanation": "DROP TABLE drops the table definition along with all its records from the database structure." }, { "id": "9c46a732-a270-487a-87e3-630be0726f7b", "q": "Which clause is used to filter query results based on aggregate functions?", "options": ["WHERE", "HAVING", "GROUP BY", "ORDER BY"], "answer": 1, "hint": "It acts as a WHERE clause for groups.", "explanation": "The HAVING clause is used to apply filter conditions to grouped attributes/aggregates." }, { "id": "22926a48-604d-456e-bd40-462f11f91151", "q": "What is the difference between DELETE and TRUNCATE SQL commands?", "options": ["DELETE is faster than TRUNCATE", "DELETE is a DDL command, TRUNCATE is DML", "DELETE can delete specific rows with WHERE; TRUNCATE deletes all rows and cannot use WHERE", "TRUNCATE deletes the table structure; DELETE does not"], "answer": 2, "hint": "TRUNCATE resets the table and is a DDL command; DELETE is row-by-row DML.", "explanation": "DELETE is DML and supports WHERE clauses to remove specific rows. TRUNCATE is DDL, instantly empties the table, and cannot have a WHERE clause." }, { "id": "a1462ecb-053b-4f9e-9047-d1eaa080faf9", "q": "Which SQL function is used to convert string characters to uppercase?", "options": ["CAPS()", "UPPER()", "UPPERCASE()", "TO_UPPER()"], "answer": 1, "hint": "Oracle/MySQL use a five-letter name.", "explanation": "The UPPER() scalar function converts all characters in a string to uppercase." }, { "id": "83b02640-34b6-4f06-a9ec-01c5c498a89d", "q": "In SQL, what constraint prevents duplicate entries and allows NULL values?", "options": ["PRIMARY KEY", "UNIQUE", "NOT NULL", "CHECK"], "answer": 1, "hint": "Unlike Primary Key, this key allows multiple NULL values.", "explanation": "The UNIQUE constraint ensures all non-NULL values in a column are distinct, while permitting NULL values." }, { "id": "f1fd8496-1bba-4426-8cc4-cf4227b81814", "q": "What is the result of using COUNT(column_name) on a column that has NULL values?", "options": ["It throws a syntax compilation error", "It counts the NULL values as 0", "It ignores the NULL values and counts only the non-NULL entries", "It returns NULL as the result"], "answer": 2, "hint": "Aggregate functions (except COUNT(*)) exclude nulls.", "explanation": "COUNT(column_name) counts only the non-NULL rows in the specified column, ignoring NULLs. COUNT(*) counts all rows." }], topicId: "3.1" })] }); } function MDXContent(props = {}) { const {wrapper: MDXLayout} = { ..._provideComponents(), ...props.components }; return MDXLayout ? _jsx(MDXLayout, { ...props, children: _jsx(_createMdxContent, { ...props }) }) : _createMdxContent(props); } return { default: MDXContent }; function _missingMdxReference(id, component) { throw new Error("Expected " + (component ? "component" : "object") + " `" + id + "` to be defined: you likely forgot to import, pass, or provide it."); }