Connection — SQL Technical Interview Questions & Answers

Connection

Connection

  • A connection is a login to the server: host + port + user + password + database. Default host is 127.0.0.1 (this computer), port 3306. From PHP/Java/Node you pass the same four things in a connection string or DSN.
  • CLI: mysql -u asha -p -h 127.0.0.1 college. Workbench: New Connection, same fields. If it fails, the error is usually wrong password, server not running, or user not allowed from that host.
  • Apps should use a normal user with rights only on that database — not root. Close the connection when the request ends (or use a pool). Leaving 500 open connections hits ‘too many connections’.
  • Trap: ‘localhost’ vs 127.0.0.1 can use a socket vs TCP on Linux — different user grants. If one works and the other doesn’t, check mysql.user host column.
  • On the example next to this theory: Connection: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Technical Interview Tip: Four fields: host, user, password, database. Port 3306.

Code Examples for Connection

Connection — sample query

-- Connection
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Connection: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Connection to an Interviewer

What do you need to connect?

Host, user, password, database Port 3306 by default

Why not root from PHP?

Too much power Make a user with GRANT only on that DB