PCS Interview Questions

diagrams which show the database entity for computing

1:1, 1:M, M:M, 1:Fixed

• personal logic input
• 1) micro block sample data approach either from in-person approach stand 
• 2) else, social media data polling depending on the promt

I understand SQL is a programming language used mainly on the statical approach compared to the mathematical engines like MatLab; the database is a list of data compiled into a table in files like Excel or .comma(dot comma).

• Database normalization is a data design and organization process that helps build a relational database table
• i.e.
• 1NF - remove repeating groups
• 2NF - remove repeating data (make table unique)
• 3NF - remove independent columns that do not contribute to the data

• DDL (data definition language)
• DML (data modification language) 
• DQL (data query language) 
• DCL (data control language)

• SYSTEM CREATE Table(
• PrimaryId int NOT NULL PRIMARY KEY,
• [insert table parameters]
• );

• It is proper to define the data type while creating a table, but it is optional in the column
• i.e. stating 'NOT NULL'

• ALTER TABLE [table name] 
• ADD [column name] [object type]
• i.e.
• ALTER TABLE customers
• ADD email_ID varchar(n);

• Join is a merge of tables based on common data
• 3 types:
• [sort] Merge - a variation of nested loops that sort each row of the table
• Hash [join] - joins to larger data sets and build a table
• [nested] Loop - joins an outer table to an inner table and gets all the data that fits the condition of the table

define the purpose or intention, organize the data into categories, establish the primary key, refine data, then set parameters or normalizations

• 1:1 -parent to child
• 1:M - teacher to students
• M:M - broken into conjunction table with composite primary keys
• 1:Fixed- set max/min relationship limit

• cluster index - defines how data are physically stored in a table
• non-cluster indexes - does not sort physical data inside a table, and the data is stored in a different place

• DDL (data definition language) - create, alter, drop
• DML (data modification language) - update, truncate, delete, insert
• DQL (data query language) - select, from, where, group, having, order
• DCL (data control language) - grant, revoke, deny

• CREATE - used to create an object, table, view
• i.e. Create Table TableA
• SELECT - SELECT '[condiction]'
• i.e. Select 'Truth'
• INSERT - add data to an existing table
• i.e. Insert into TableA values(n, ' ', ' ')
• UPDATE - change the value in a row
• i.e. Update TableA; Set column = ' '; Where column = 'hai'
• DELETE - remove rows of data from table
• i.e. Delete; From Table; Where column =1
• DROP - remove an object from database
• i.e. Drop table; TableA

• ER diagram is a map that shows where every object are connected
• i.e. what would be done during daytime normally and what would not be at nighttime

• 1NF - remove repeating groups
• 2NF - remove repeating data (make table unique)
• 3NF - remove independent columns that do not contribute to the data

• delete - delete rows of data from a table
• truncate - remove ALL rows of data from a table
• drop - remove an object from the database

Constraints are the rules for the table

• Primary Key, Unique Key, Foreign Key
• Not Null, Check, Default

Used to combine tables based on the data commonality. Joins have inner, left, left outer, right, right outer, full, self, and cross

• Subqueries are written inside of the parenthesis of the query whereas joins are a merge of table data.
• types: single row, multiple rows, multiple columns, correlated and nested

help measures statement accomplishment, enhances decision making, provides clearer insight through data visualization, efficiency, and updated

a named group of SQL statements that have been created previously and stored in the server database

Pro: promote code reuse, reduces network and latency, boost performance, execution plans can be reused, stay cached, reduce server overhead, provide better security to data

Con: store procedure is fixed and not flexible if need multiple changes, not event-driven (from Trigger function)

Execution plan can tell you how a query will be executed; used as a debugger 

2 distinct plans: estimated and actual

• estimated - the plan that represents the output from the optimizer (the logical plan)
• actual- the plan that represents the output from the actual query execution (the actual step by step plan)

• Can the plan be changed?
• Yes, the plan can be changed

a SQL procedure that initiates an action when an event (INSERT, DELETE, or UPDATE) is called; nested trigger is plausible.

Local: can only be called from within the program if pre-set before

Global: can be called from the library database from anywhere in the program

are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

types: scalar, inline (expanded function), and multi-table statement

• clauses used to specify searching conditions for a group or an aggregate(a group of combined elements).
• Consists: SELECT, FROM, WHERE, GROUP, HAVING, ORDER

• ALTER is used to modify a specified row, rows, or columns in the database of the table
• used for add, delete, modify, modify

?

CREATE TABLE Customer( CID int, Name varchar(n), Bdate int);

CREATE TABLE Orders( OID int, PID int, Quantity int, Date int);

Create Table Products( PID0 int, PNamePrice int, Category varchar(n), Subcategory varch(n) );

• Select [CID], [Name], [Birthdate] 
• from Customer
• where [Birthdate] = (select max([Birthdate]) from Customer)

• Select [OID], [CID], [PID], [Quantity,] [Date]
• from Orders
• where OID = (select max([OID]) from Orders);

• Select OID, CID, PID, Quantity, Date
• From Orders
• Where CID = (select Quantity <= 0);

• Alter Table Customer
• Add gender varch(1);

• select count(*) as tables
• from sys.tables

• output for i:
• 1, 3, 9

i count: 2, 4, 6, 

• output for j:
• 1, 2, 3, 4, 5, 6, 7, ..., 13