SQL Interview Questions for Freshers

“SQL is a language. It's the standard way to ask a relational database for data or to change it, with commands like SELECT, INSERT and UPDATE. A DBMS is the software that actually stores the data, runs my queries, lets many people use it at once and keeps the data safe if something crashes. A relational DBMS keeps data in tables of rows and columns and links them with keys. MySQL, PostgreSQL, SQL Server and Oracle are all relational database products. They all understand SQL, but each adds its own extras, so small things differ. For example, to get only the first few rows you write LIMIT in MySQL and PostgreSQL, TOP in SQL Server, and FETCH FIRST in standard SQL. In my college project I used MySQL, but most of my queries would run on the others with small changes.”

“DDL is data definition language. These commands shape the structure: CREATE, ALTER, DROP and TRUNCATE. DML is data manipulation language: INSERT, UPDATE and DELETE, which change the rows inside tables. Some people put SELECT in DML too, and some give it its own group called DQL. DCL is data control language, which is GRANT and REVOKE, deciding who's allowed to do what. TCL is transaction control: COMMIT, ROLLBACK and SAVEPOINT, which decide whether a group of changes is kept or undone. One thing worth knowing is that in MySQL and Oracle a DDL command commits on its own, so you can't roll back a DROP there, while PostgreSQL and SQL Server let you run most DDL inside a transaction and roll it back.”

“I'd start with what the data looks like. If it has clear relationships, like students, courses and enrolments, a SQL database fits well: fixed tables, keys that link them, joins, and transactions so a change either fully happens or doesn't. NoSQL isn't one thing. It's a family that includes document stores, key-value stores, wide-column and graph databases. A document store makes sense when records have different shapes, or when I mostly read and write one whole object at a time, like a user's settings. Many of them are also built to spread across lots of servers. For my project, a hostel booking app, I chose PostgreSQL because bookings had to be consistent. Two students must never get the same bed, and a unique constraint plus a transaction handled that cleanly.”

“ALTER TABLE is DDL. It changes the shape of the table itself: adding or dropping a column, changing a column's type, or adding a constraint. UPDATE is DML. It leaves the structure alone and changes values in rows that already exist. So if the college decides to store phone numbers, I ALTER the table to add a phone column. Every existing row gets the new column, empty for now. Then I UPDATE rows to fill in the numbers. The easy way I remember it: ALTER talks about the table and its columns, UPDATE talks about rows and values. And an UPDATE with no WHERE changes every row, so I always check the WHERE before running it.”

“The update itself is short: set marks to marks plus five where section is B. The risky part is the WHERE, because an UPDATE without one changes every row in the table. So first I run a SELECT with exactly the same WHERE and check that the rows and the count look right. Then I run the UPDATE inside a transaction. The database tells me how many rows changed, and if that matches what the SELECT showed, I commit. If not, I roll back and nothing is lost. SQL Server wants BEGIN TRANSACTION, and Oracle starts a transaction on its own, so there I skip BEGIN. I'd also ask whether marks have a maximum, because adding five could push someone over it.”

“CHAR is fixed length. If I declare CHAR(10) and store abc, the database pads it with spaces to ten characters. VARCHAR is variable length. VARCHAR(10) stores just what I give it, up to ten characters, plus a small marker for the length. So CHAR suits values that are always the same size, like a two-letter country code, and VARCHAR suits things that vary, like a name. For a phone number I'd use VARCHAR, not a number type. Phone numbers can start with a zero or a plus sign, they can be longer than a normal integer holds, and I never do maths on them. An integer column would quietly drop a leading zero. How trailing spaces are compared differs between databases, so I don't rely on that.”

“This is a many-to-many relationship. One student takes many courses and one course has many students, so I need a table in the middle where each row is one student in one course. I'd make the primary key the pair student_id and course_id together. That's a composite key: neither column is unique on its own, but the pair is, so the database itself refuses a second row for the same student and course. Both columns are also foreign keys, so nobody can enrol a student or a course that doesn't exist. Grade stays empty until results come out, so I leave it nullable. If the team prefers a single id column on every table, that's fine, but then I'd still add a unique constraint on the pair, or duplicates slip in.”

“Say the table has student_id, student_name, course_id, course_name, teacher_name, teacher_phone and grade, and the key is student_id plus course_id. First normal form means one value per cell, so no column holding a list of subjects. Second normal form means every non-key column depends on the whole key. Student_name depends only on student_id, and the course and teacher columns depend only on course_id, so they move out into a students table and a courses table. Only grade needs both, so it stays in enrolments. Third normal form removes columns that depend on another non-key column. Courses now holds teacher_name and teacher_phone, but the phone depends on the teacher, not the course. So I make a teachers table and keep just teacher_id in courses. Now a teacher's new phone number is one update, not fifty.”

“I'd say it's great that it works, but it'll hurt us soon, and I'd show why with real queries. Finding every student in course 104 means matching text, and a careless pattern also matches 1045. Counting students per course, removing one course from a student, or joining to the courses table for names all turn into messy string work. The database also can't check that 107 is a real course, because it's just text, not a foreign key. The fix is an enrolments table with one row per student and course, which is exactly what first normal form asks for. I'd offer to write the change and update the few queries that use the column, so it doesn't feel like I'm handing them extra work, and I'd suggest we do it now while the data is small.”

“LIKE matches patterns, and it has two wildcards. The percent sign stands for any number of characters, including none. The underscore stands for exactly one character. So A followed by a percent sign means the name starts with A and anything can follow. For the third letter, I put two underscores for the first two letters, then r, then a percent sign for whatever comes after. Whether a lowercase a matches a capital A depends on the database. MySQL is usually case-insensitive by default, PostgreSQL isn't, so there I'd use ILIKE or apply LOWER to the column. And a pattern that starts with a wildcard can't use a normal index well, so on a big table, searching for names that end in something is slow.”

“AND is evaluated before OR, the same way multiplication comes before addition. So the database reads it as: dept is CS, or dept is IT and marks are above 80. Every CS student comes back whatever their marks, and only the IT students get the marks filter. The fix is brackets around the two departments, so the OR is settled first and the marks condition then applies to both. Even cleaner is IN with a list, which says exactly what I mean in one step. My habit now is to add brackets whenever I mix AND and OR, even when I'm sure of the order, because the next person reading the query shouldn't have to work it out.”

“COUNT(*) counts rows, all of them, whatever is in the columns. COUNT(email) counts only the rows where email is not NULL, so if twenty students haven't given an email, it comes back twenty lower than COUNT(*). COUNT(DISTINCT city) counts how many different non-NULL cities there are, so a hundred students from four cities gives four. I use the gap between the first two as a quick data check: COUNT(*) minus COUNT(email) tells me how many emails are missing. People sometimes say COUNT(1) is faster than COUNT(*), but in the databases I've used they're handled the same way, so I just write COUNT(*).”

“GROUP BY dept squashes all the rows for one department into a single row, and COUNT(*) counts how many rows went into each group. ORDER BY with DESC puts the biggest department first, and I can sort by the alias because ORDER BY runs after SELECT. If I add name to the SELECT, the database doesn't know which name to show, because one department row stands for many students. So the rule is that every column in the SELECT must be either in the GROUP BY or inside an aggregate like COUNT, MAX or MIN. Most databases throw an error. Older MySQL settings used to let it run and pick any name, which is worse, because the answer looks fine but is random.”

“MAX is an aggregate, so it collapses the whole table into one value. Asking for name next to it is the same problem as grouping without name: the output has one row and there's no rule for which name to put in it, so most databases give an error. The clean fix is a subquery. The inner query finds the highest marks, and the outer query returns every student who has exactly that score. I like this version because if two students tie for the top, both come back, which is usually what you want. Sorting by marks descending and taking one row with LIMIT also works, but it silently drops the second student in a tie.”

“An inner join would drop the empty departments, because they have no matching student rows. So I start from departments and LEFT JOIN students. An empty department still appears, once, with NULL in every student column. Now the trap: if I write COUNT(*), that one row of NULLs counts as a row, so an empty department shows one student. COUNT(s.id) counts only non-NULL ids, so it correctly gives zero. I group by the department id as well as the name, in case two departments share a name. I'd test it by adding a department with no students and checking it shows zero.”

“The enrolments table is the link. Each row says one student took one course and got a grade. So I start from enrolments, join students on the student id to get the name, then join courses on the course id to get the title. Each join needs its own ON condition. If I match one on the wrong column, the rows pair up wrongly without any error, so I read each ON line carefully. I use short aliases so the query stays readable and it's clear which table each column comes from. These are inner joins, so a student with no enrolments won't appear. If the task was to list every student, even those with no courses, I'd start from students and LEFT JOIN both enrolments and courses.”

“A CROSS JOIN pairs every row of one table with every row of the other, with no join condition. If one table has three rows and the other has four, I get twelve. That's called a Cartesian product. It's useful when I really want every combination, like every T-shirt size with every colour to create product variants, or every student with every subject to build an empty marks sheet for teachers to fill in. The accident happens with the old comma style, FROM students, courses, when someone forgets the WHERE condition that links them. A thousand students and fifty courses suddenly gives fifty thousand rows. That's one reason I write explicit JOIN with ON, because a missing condition is much easier to spot.”

“The equals sign compares one value with one value. It worked while block A had one department, but once a second department moved into block A, the subquery returned two ids, and the database can't compare dept_id with a list. So the fix is IN, which checks whether dept_id matches any value in the list. A subquery that returns exactly one value is called a scalar subquery, and that's the kind you can use with equals, like comparing marks with MAX(marks). One that can return many rows needs IN, EXISTS or a join. What I wouldn't do is add LIMIT 1 to silence the error, because that picks one department and quietly ignores the rest.”

“I turn every course into a count. For each student, I count how many different courses they're enrolled in, and keep only the students whose count equals the total number of courses. The join brings in their enrolments, GROUP BY gives one row per student, and HAVING filters on the count, because WHERE can't see aggregates. DISTINCT inside the count matters if the same course can appear twice for a student, say after a repeat. One assumption I'd say out loud: this is right only if enrolments point at courses that exist, which a foreign key guarantees. There's also a double NOT EXISTS version, find students for whom no course exists that they're not enrolled in. It's harder to read, but it's the classic answer to this kind of question, which is called relational division.”

“CASE checks its WHEN conditions from top to bottom and stops at the first one that's true. That's why I write them from highest to lowest. A mark of 75 fails the first check, passes the check for 60 and above, and becomes B, so I don't need to write between 60 and 79. ELSE catches everything else. One subtle point: a student with NULL marks fails every comparison and lands in ELSE, so they'd show as Fail. If that's wrong, I'd add a first line that labels NULL as Absent. To count per label, I put the CASE in a subquery and group by the label. Some databases let you group by the alias directly, but the subquery works everywhere.”

“If I run two separate updates, first Y to N and then N to Y, the second one flips everything back and I end up with all Y. The trick is to do both in one statement with CASE. The database works out each row's new value from that row's old value, so every Y becomes N and every N becomes Y in the same step. I add ELSE is_active so any other value, like a NULL or a typo, stays as it was. Without that ELSE, those rows would be set to NULL, which is a quiet way to damage data. There's no WHERE because the task really is every row, but I'd still check the counts of Y and N before and after.”

“CONCAT joins the two names with a space between them, UPPER turns the result into capitals, and LENGTH counts the characters. I repeat the expression in WHERE rather than using the alias, because WHERE runs before SELECT and can't see full_name yet. These functions vary a little. SQL Server calls it LEN, MySQL's LENGTH counts bytes, so CHAR_LENGTH is safer for accented names, and Oracle's CONCAT takes only two values, so there I'd use the double pipe operator. NULLs matter too. In MySQL, CONCAT returns NULL if any part is NULL, while PostgreSQL's CONCAT simply skips NULL parts. So if last_name can be missing, I'd wrap it in COALESCE to be safe.”

“In my final-year project we built a canteen pre-order app. The database was PostgreSQL, with five tables: users, menu_items, orders, order_items and payments. Orders held who ordered and when, and order_items linked each order to menu items with a quantity, since one order has many items. I copied the price into order_items at the time of ordering, because menu prices changed during the semester and old bills had to stay correct. What I'd do differently: we stored the order status as free text, and ended up with Ready, ready and READY in the data. Now I'd use a CHECK constraint or a small lookup table so only valid values get in. I'd also add indexes on the foreign key columns earlier, because the orders screen slowed down once we loaded test data.”

“In our DBMS lab we used PostgreSQL, and one task was to show the pass rate for each subject. My query divided the number of students who passed by the total, and every subject came out as zero. I knew that was impossible, since most people passed. I ran the two counts on their own and both looked right, so the problem had to be the division. That's when I learned that dividing one integer by another in PostgreSQL gives an integer, so 45 divided by 60 is cut down to zero. I fixed it by multiplying by 100.0 first, which makes the whole calculation decimal, then rounding to one place. MySQL would have given a decimal here, so now I test the maths on a tiny case I can check by hand.”