Cs Interview Cheat Sheet

Posted : admin On 1/3/2022

Simran works at Hackr as a technical writer. The graduate in MS Computer Science from the well known CS hub, aka Silicon Valley, is also an editor of the website. She enjoys writing about any tech topic, including programming, algorithms, cloud, data science, and AI. Traveling, sketching, and gardening are the hobbies that interest her. EXPERIENCE LEAGUE. Find Experience League learning pathways. Join the Experience League community. Start your AEM guided onboarding journey. Build Analytics skills with curated help topics. Creating a cheat sheet will help you feel more prepared and confident. You shouldn't memorize what's on the sheet or check it off during the interview. You should use your cheat sheet to remind you of key facts. Here are some suggestions for what you should include on it. In the days before the interview. Draw a line down the center of a piece of paper. Introducing: The Job Interview Cheat Sheet The Job Interview Cheat Sheet is a 31-page PDF packed with the exact steps I’ve tested, fine-tuned, and recommended to my clients for years as a Recruiter (but haven’t shared anywhere else until now). Big O time costs and core properties for dynamic arrays, linked lists, queues, stacks, hash tables, binary search trees, tries, heaps, priority queues, bloom filters, and LRU caches.

  • SQL Cheat Sheet
    • Background: What is SQL? Why do we need it?
    • Managing Tables
    • Manipulating Data
    • Retrieving Attributes
    • JOINS
    • Subqueries
    • Using Functions to Customize ResultSet
    • GROUPING DATA

SQL Cheat Sheet

Background: What is SQL? Why do we need it?

SQL is a database language used to query and manipulate the data in the database.

Main objectives:

  • To provide an efficient and convenient environment
  • Manage information about users who interact with the DBMS

The SQL statements can be categorized as

Data Definition Language(DDL) Commands:

  • CREATE: creates a new database object, such as a table.
  • ALTER: used to modify the database object
  • DROP: used to delete the objects.

Data Manipulation Language(DML) Commands:

  • INSERT: used to insert a new data row record in a table.
  • UPDATE: used to modify an existing record in a table.
  • DELETE: used delete a record from the table.

Data Control Language(DCL) Commands:

  • GRANT: used to assign permission to users to access database objects.
  • REVOKE: used to deny permission to users to access database objects.

Data Query Language(DQL) Commands:

  • SELECT: it is the DQL command to select data from the database.

Data Transfer Language(DTL) Commands:

  • COMMIT: used to save any transaction into the database permanently.
  • ROLLBACK: restores the database to the last committed state.

Identifying Data Types

Data types specify the type of data that an object can contain, such as integer data or character data. We need to specify the data type according to the data to be stored.

Following are some of the essential data types:

Data TypeUsed to Store
intInteger data
smallintInteger data
tinyintInteger data
bigintInteger data
decimalNumeric data type with a fixed precision and scale.
numericnumeric data type with a fixed precision and scale.
floatfloating precision data
moneymonetary data
datetimedata and time data
char(n)fixed length character data
varchar(n)variable length character data
textcharacter string
bitinteger data with 0 or 1
imagevariable length binary data to store images
realfloating precision number
binaryfixed length binary data
cursorcursor reference
sql_variantdifferent data types
timestampunique number in the database that is updated every time in a row that contains timestamp is inserted or updated.
tabletemporary set of rows returned as a result set of a table-valued function.
xmlstore and return xml values

Managing Tables

Create Table

Table can be created using the CREATE TABLE statement. The syntax is as follows:

Example: Create a table named EmployeeLeave in Human Resource schema with the following attributes:

ColumnsData TypeChecks
EmployeeIDintNOT NULL
LeaveStartDatedateNOT NULL
LeaveEndDatedateNOT NULL
LeaveReasonvarchar(100)NOT NULL
LeaveTypechar(2)NOT NULL
Constraints in SQL

Constraints define rules that must be followed to maintain consistency and correctness of data. A constraint can be created by using either of the following statements:

Types of Constraints:
ConstraintDescriptionSyntax
Primary keyColumns or columns that uniquely identify all rows in the table.

CREATE TABLE table_name

( col_name [CONSTRAINT constraint_name PRIMARY KEY] (col_name(s))

)

Unique keyEnforces uniqueness on non primary key columns.
Foreign keyIs used to remove the inconsistency in two tables when the data depends on other tables.
CheckEnforce domain integrity by restricting the values to be inserted in the column.

3.2 Modifying Tables

Modify table using ALTER TABLE statement when:

  1. Adding column
  2. Altering data type
  3. Adding or removing constraints

Syntax of ALTER TABLE:

Renaming a Table

A table can be renamed whenever required using RENAME TABLE statement:

RENAME TABLE old_table_name TO new_table_name;

Dropping a Table versus Truncate Table

A table can be dropped or deleted when no longer required using DROP TABLE statement:

The contents of the table can be deleted when no longer required without deleting the table itself using TRUNCATE TABLE statement:

Cs Interview Cheat Sheet

Manipulating Data

Storing Data in a Table

Syntax:

Printable job interview cheat sheet

Example: Inserting data into Student table.

Example: Inserting multiple data into Student table.

Copying Data from one table to another:

Updating Data in a Table

Data can be updated in the table using UPDATE DML statement:

Example update marks of Andy to 85

Deleting Data from a Table

Cheat

A row can be deleted when no longer required using DELETE DML statement.

Syntax:

Deleting all records from a table:

Retrieving Attributes

One or more column can be displayed while retrieving data from the table.

One may want to view all the details of the Employee table or might want to view few columns.

Required data can be retrieved data from the database tables by using the SELECT statement.

The syntax of SELECT statement is:

Consider the following Student table:

StudentIDFirstNameLastNameMarks
101JohnRay78
102SteveJobs89
103BenMatt77
104RonNeil65
105AndyClifton65
106ParkJin90

Retrieving Selected Rows

To retrieve selected rows from a table use WHERE clause in the SELECT statement.

HAVING Clause is used instead of WHERE for aggregate functions.

Comparison Operators

Comparison operators test for the similarity between two expressions.

Syntax:

Example of some comparison operators:

Logical Operators

Logical operators are used to SELECT statement to retrieve records based on one or more conditions. More than one logical operator can be combined to apply multiple search conditions.

Syntax:

Types of Logical Operators:
OR Operator
AND Operator
NOT Operator

Range Operator

Range operator retrieves data based on range.

Syntax:

Types of Range operators:
BETWEEN
NOT BETWEEN

Retrieve Records That Match a Pattern

Data from the table can be retrieved that match a specific pattern.

The LIKE keyword matches the given character string with a specific pattern.

Displaying in a Sequence

Use ORDER BY clause to display the data retrieved in a specific order.

Displaying without Duplication

The DISTINCT keyword is used to eliminate rows with duplicate values in a column.

Syntax:

JOINS

Joins are used to retrieve data from more than one table together as a part of a single result set. Two or more tables can be joined based on a common attribute.

Types of JOINS:

Consider two tables Employees and EmployeeSalary

EmployeeID (PK)FirstNameLastNameTitle
1001RonBrentDeveloper
1002AlexMattManager
1003RayMaxiTester
1004AugustBergQuality
EmployeeID (FK)DepartmentSalary
1001Application65000
1002Digital Marketing75000
1003Web45000
1004Software Tools68000
INNER JOIN

An inner join retrieves records from multiple tables by using a comparison operator on a common column.

Syntax:

Example:

OUTER JOIN

An outer join displays the resulting set containing all the rows from one table and the matching rows from another table.

An outer join displays NULL for the column of the related table where it does not find matching records.

Syntax:

Types of Outer Join

LEFT OUTER JOIN: In left outer join all rows from the table on the left side of the LEFT OUTER JOIN keyword is returned, and the matching rows from the table specified on the right side are returned the result set.

Example:

RIGHT OUTER JOIN: In right outer join all rows from the table on the right side of the RIGHT OUTER JOIN keyword are returned, and the matching rows from the table specified on the left side are returned is the result set.

Example:

FULL OUTER JOIN: It is a combination of left outer join and right outer join. This outer join returns all the matching and non-matching rows from both tables. Whilst, the matching records are displayed only once.

Interview

Example:

CROSS JOIN

Also known as the Cartesian Product between two tables joins each row from one table with each row of another table. The rows in the result set is the count of rows in the first table times the count of rows in the second table.

Syntax:

EQUI JOIN

An Equi join is the same as inner join and joins tables with the help of foreign key except this join is used to display all columns from both tables.

SELF JOIN

In self join, a table is joined with itself. As a result, one row is in a table correlates with other rows in the same table. In this join, a table name is mentioned twice in the query. Hence, to differentiate the two instances of a single table, the table is given two aliases. Syntax:

Subqueries

An SQL statement that is used inside another SQL statement is termed as a subquery.

They are nested inside WHERE or HAVING clause of SELECT, INSERT, UPDATE and DELETE statements.

  • Outer Query: Query that represents the parent query.
  • Inner Query: Query that represents the subquery.

Using IN Keyword

If a subquery returns more than one value, we might execute the outer query if the values within the columns specified in the condition match any value in the result set of the subquery.

Syntax:

Using EXISTS Keyword

EXISTS clause is used with subquery to check if a set of records exists.

TRUE value is returned by the subquery in case if the subquery returns any row.

Syntax:

Using Nested Subqueries

A subquery can contain more than one subqueries. Subqueries are used when the condition of a query is dependent on the result of another query, which is, in turn, is dependent on the result of another subquery.

Syntax:

Correlated Subquery

A correlated subquery can be defined as a query that depends on the outer query for its evaluation.

Using Functions to Customize ResultSet

Various in-built functions can be used to customize the result set.

Syntax:

Using String Functions

String values in the result set can be manipulated by using string functions.

They are used with char and varchar data types.

Following are the commonly used string functions are:

Function NameExample
left
len
lower
reverse
right
space
str
substring
upper

Using Date Functions

Date functions are used to manipulate date time values or to parse the date values.

Date parsing includes extracting components, such as day, month, and year from a date value.

Some of the commonly used date functions are:

Function NameParametersDescription
dateadd(date part, number, date)Adds the number of date parts to the date.
datediff(date part, date1, date2)Calculates the number of date parts between two dates.
Datename(date part, date)Returns date part from the listed as a character value.
datepart(date part, date)Returns date part from the listed as an integer.
getdate0Returns current date and time
day(date)Returns an integer, which represents the day.
month(date)Returns an integer, which represents the month.
year(date)Returns an integer, which represents the year.

Using Mathematical Functions

Numeric values in a result set can be manipulated in using mathematical functions.

The following table lists the mathematical functions:

Function NameParametersDescription
abs(numeric_expression)Returns an absolute value
acts,asin,atan(float_expression)Returns an angle in radians
cos, sin, cot,tan(float_expression)Returns the cosine, sine, cotangent, or tangent of the angle in radians.
degrees(numeric_expression)Returns the smallest integer greater than or equal to specifies value.
exp(float_expression)Returns the exponential value of the specified value.
floor(numeric_expression)Returns the largest integer less than or equal to the specified value.
log(float_expression)Returns the natural logarithm of the specified value.
pi0Returns the constant value of 3.141592653589793
power(numeric_expression,y)Returns the value of numeric expression to the value of y
radians(numeric_expression)Converts from degrees to radians.
rand([seed])Returns a random float number between 0 and 1.
round(numeric_expression,length)Returns a numeric expression rounded off to the length specified as an integer expression.
sign(numeric_expression)Returns positive, negative or zero.
sqrt(float_expression)Returns the square root of the specified value.

Using Ranking Functions

Ranking functions are used to generate sequential numbers for each row to give a rank based on specific criteria.

Ranking functions return a ranking value for each row. Following functions are used to rank the records:

  • row_number Function: This function returns the sequential numbers, starting at 1, for the rows in a result set based on a column.
  • rank Function: This function returns the rank of each row in a result set based on specified criteria.
  • dense_rank Function: The dense_rank() function is used where consecutive ranking values need to be given based on specified criteria.

These functions use the OVER clause that determines the ascending or descending sequence in which rows are assigned a rank.

Using Aggregate Functions

The aggregate functions, on execution, summarize the values for a column or group of columns and produce a single value.

Syntax:

Following are the aggregate functions:

Cheat
Function NameDescription
avgreturns the average of values in a numeric expression, either all or distinct.
countreturns the number of values in an expression, either all or distinct.
minreturns the lowest value in an expression.
maxreturns the highest value in an expression.
sumreturns the total of values in an expression, either all or distinct.

GROUPING DATA

Grouping data means to view data that match a specific criteria to be displayed together in the result set.

Data can be grouped by using GROUP BY, COMPUTE,COMPUTE BY and PIVOT clause in the SELECT statement.

GROUP BY Clause

Summarizes the result set into groups as defined in the query by using aggregate functions.

Syntax:

COMPUTE and COMPUTE BY Clause

This COMPUTE clause, with the SELECT statement, is used to generate summary rows by using aggregate functions in the query result.

The COMPUTE BY clause can be used to calculate summary values of the result set on a group of data.

Syntax:

PIVOT Clause

The PIVOT operator is used to transform a set of columns into values, PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

Syntax:

People are also reading:


  • PDF Link: cheatsheet-systemdesign-A4.pdf, Category: interview
  • Blog URL: https://cheatsheet.dennyzhang.com/cheatsheet-systemdesign-A4
  • Related posts: CheatSheet: Leetcode For Code Interview, CheatSheet: Well-Known Papers For IT Industry, #denny-cheatsheets

File me Issues or star this repo.

1.1 Reference

Cheat Sheet For Interview Questions

NameSummary
YouTubeYouTube: Intro to Architecture and Systems Design Interviews, YouTube: System Design Interview
YouTubeYouTube Channel: Success in Tech, YouTube: Scalability Harvard Web Development
YouTubeYouTube: Prepare for Your Google Interview: Systems Design
PapersCheatSheet: Well-Known Papers For IT Industry, Github: papers-we-love
GithubGithub: donnemartin/system-design-primer, Github: checkcheckzz/system-design-interview
GithubGithub: puncsky/system-design-and-architecture, Github: yangshun/tech-interview-handbook
WebsiteWebsite: hiredintech – System Design, Website: blog.gainlo.co
WebsiteWebsite: interviewing.io, Website: interviewbit.com
CheatsheetCheatSheet: Leetcode For Code Interview, CheatSheet: Common Code Problems & Follow-ups
CheatsheetCheatSheet: System Design For Job Interview, CheatSheet: SRE/DevOps/Sysadmin
CheatsheetCheatSheet: Behavior Questions For Coder Interview, Programming Language Implemenetations CheatSheet
CheatsheetCheatSheet: Concurrency & Parallel Programming
CodingCode problems for #oodesign
Individual Tech BlogBlog: highscalability, Blog: All Things Distributed – Amazon CTO
Company Tech BlogWebsite: Facebook Engineering, Website: Google Developers
Company Tech BlogMedium: Netflix Blog, Medium: Airbnb Engineering & Data Science
Company Tech BlogMedium: Instagram Engineering, Medium: Mixpanel
Company Tech BlogShopify Engineering, Github Engineering
Company Tech BlogDoordash Engineering, Uber Engineering
ReferenceLink: Facebook Engineering Interview, Link: The System Design Process

1.2 Design Problems Per Category

NumNameSummary
1K/V DB storeDesign K/V DB; Design memcache/redis
2Data synchronizationDesign dropbox client sync
3Resource/Task schedulingDesign web crawler; Delayed task queue; Design a distributed message queue
4Design a distributed componentDesign a distributed hit counter, Design a distributed UUID generator
5Design a SNS systemDesign Twitter News Feed
6Design API GatewayDesign An API Rate Limiter
7Design a logging & metrics systemPull vs Push model
8Design a gaming systemDesign: Leaderboard Ranking
10Design a small scale MIS systemDesign: Flight booking service, Design a payment processor
11Recommendation systemDesign amazon book recommendation system
12Design a communication systemDesign a message chat room
13Design an ads system

1.3 Top 50 Component Design

NumNameSummary
1Top K Frequent Elements in Recent X minsGithub: link
2Design An API Rate Limiter
3Design: Leaderboard Ranking
4Delayed task queue
5Spam Filter: design a system to block malicious IPsGithub: link
6Find duplicates files across 1000 servers with 10 million filesGithub: link
7Design a monitoring system to check 10,000 nodesGithub: link
8Design a scalable and reliable notification serviceGithub: link
9Web crawler for 1 billion URL from 1 seed URLGithub: link
10Design twitter timeline featureGithub: link
11How to upload large videos at scaleGithub: link
12Real-time Deduping At ScaleGithub: link
13How quoram based DB works when nodes join or leave
14How to implement redis clusteringGithub: link
15How to deployment 1GB binary to 10,000 serversGithub: link
16How to distribute TB data from a server to 10,000 nodes
17Merge big datasets across different serversGithub: link
18Unique url hits
19Design a distributed counter
20Design a distributed message queue
21Design a distributed cache service
22Design a distributed Hashmap
23Design a distributed UUID generator
24Design a git service
25Design: A Parking Lot Service
26Design a distributed transaction
27Design: A URL Redirecting Feature
28Give three 1TB disks, how to store 2TB data with redundancyGithub: link. XOR bit manipulation
29How to support feature of “diff big1.bin big2.bin”#lcs – Longest Common Subsequence
30How to support “rsync big1.bin ssh:/big2.bin” in a doggy networkdelta-transfer algorithm. Weak Hashing + Strong Hashing
31Avoid double payment in a distributed payment systemLink: Avoiding Double Payments in a Distributed Payments System

1.4 Concurrency Problems

1.5 Top 30 Product Design

NumName
1Design: TinyURL – A URL Shorterner Service
2Design Twitter News Feed
3Design K/V DB
4Design autocomplete/typeahead
5Design a online contest system like leetcode.com
6Design Google Calendar
7Design a load balancer
8Design: Flight booking service
9Design: Uber Backend
10Design: An Elevator Service
11Design amazon shopping cart
12Design: Google Suggestion Service
13Design a payment processor
14Design google doc
15Design gmail
16Design RSS news reader
17Design a client-server API to build a rich document editor
18Design instagram, a photo sharing app
19Design Yelp, a location-based system
20Design Pastebin.com
21Design amazon book recommendation system
22Design Google PageRank
23Design messaging/notification system
24Design memcache/redis
25Design a voice conference system
26Design an API gateway
27Design slack
28Design a service auto-discovery feature
29Design a secrets management system
30Design Google Adsense fraud detection
31Design The Great Firewall

1.6 Process Of System Design

NumNameSummary
1Outline use cases: List major and focus on someShow good sense. The questions you asked define your level
2Estimate scale: Data + TrafficBack-of-the-envelope estimation
3Defining data modelIt helps to clarify how data will flow among different components
4Abstract designSketch main components, explain workflow, avoid too deep for details
5Detailed design + discussion with interviewersExplain trade-off of your proposal + on-demand deep dive
6Identify and resolve BottlenecksKey challenges + Trade-Offs. Usuaully no optimal solution(s)
7Scale your designAvailability, Resiliency, Scalability, Security, Serviceability, etc
8Show your relevant experience and learningIndustry best practice; You experience of scaling/trade-off/resiliency

1.7 Common Mistakes Of System Design

NumNameSummary
1Run into an opinioned solutions before clarificationInexperienced; Hard to communicate
2Not driving the conversationInexperienced
3General answers without your personal experience/thinking
4Makes interviewers feeling you’re stubborn

1.8 Top 30 Concepts For Feature/System Design

NumNameSummary
1CachingStores data so that future requests of data retrieval can be faster
2Message QueueProvides an asynchronous communications protocol,
3Data Partition & ShardingBreak up a big data volume into many smaller parts
4DB IndexingCreate indexes on multiple columns to speed up table look up
5DB replicationDuplicate data to increase service availability
6CAP: Consistency/Availability/PartitionA distributed database system can only have 2 of the 3
7DB: SQL & NoSQLRelational databases and non-relational databases
8Concurrency & Communication
9Pessimistic And Optimistic Locking
10Consistency Moduleweak consistency, eventual consistency, strong consistency
11Conflict resolutionQuorum, vector lock, reconcile on read/write, CRDTs
12B+ Tree
13Networking: HTTP
14Networking: TCP/UDP
15Pull vs Push model
16Garbage Collection
17Memory Management
18Heartbeats
19Self ProtectionAPI Rate limit, Circuit breaker, bulkhead, throttling
20Filesystem
21API: RPC vs gRPC vs REST
22Load balancer
23Scale up vs Scale outVertical scaling and Horizontal scaling
24API Design
25Session management
26Networking: TCP vs UDP
27Consistency patternsWeak consistency, Eventual consistency, Strong consistency
28Availability patternsFail-over vs Replication
29CDN – Content Delivery NetworkEdge caching
30Monitoring
31Security
32Networking: DNS
33Linux signals

1.9 Top 20 Advanced Data Structure & Algorithms

NumNameSummary
1Consistent Hash
2Bloom filterA space-effcient query returns either “possibly in set” or “definitely not”
3hyerloglog for count-distinct problemEstimation: the count of unique values with relatively high accuracy(98%)
4Reservoir Sampling
5Merkle Tree
6LPM(Longest Prefix Match)
7Frugal Streaming
8GossipPropagate cluster status
9Vector Clocks/Version Vectors
10Lossy Counting
11Skip list
12CRDTs (Conflict-Free Replicated Data Types)
13choice-of-2 in load balancer
14Range-based query
15SSTable (Sorted Strings Table)
16MemTable
17LSM (Log Structured Merge Trees)
18Two-phase commit/Three-phase commitGithub: link
19Paxos and raft protocol
20Ring buffer
21cuckoo hashingResolve hash collisions with worst-case constant lookup time
22snappy/lzssFast data compression and decompression
23S2 GeometryBuild geographic database in a better way
24geohash
25Quadtree
26DHT – distributed hash table

1.10 Explain tools: how XXX supports XXX?

NumNameSummary
1How JDK implement hashmap?
2Explain java garbage collection model
3Explain raft/etcd
4How OS supports XXX?

1.11 Cloud Design Principles

NumNameSummary
1Fail fast
2Design for failure
3Immutable infrastructure
4Cats vs CattleAvoid snowflake servers
5Auto healing
6Async programming
7GitOps operational model
8Event-Driven Architectures

1.12 Cloud Design Patterns

Cs Interview Cheat Sheet 2020

NumNameSummary
1Ambassador patternCreate helper service to send network requests, besides the main sevice
2Cache-Aside patternLoad data on demand into a cache from a data store
3Circuit Breaker patternIf a request takes too many reousrce, abort it
4Bulkhead patternIsolate elements into pools, so that one fire won’t burn all
5Gateway Aggregation patternAggregate multiple individual requests into a single request
6Priority Queue patternSupport different SLAs for different individual clients
7Strangler patternIncrementally migrate a legacy system piece by piece

1.13 Engineering Of Well-Known Products

NameSummary
GoogleLink: Google Architecture
FacebookLink: Facebook Live Streams
TwitterLink: Twitter Image Service, YouTube: Timelines at Scale
UberLink: Lessons Learned From Scaling Uber
TumblrLink: Tumblr Architecture
StackOverflowLink: Stack Overflow Architecture

1.14 Grow Design Expertise In Daily Work

NumNameSummary
1Keep the curiosityThinking about interesting/weird questions helps
2Deep dive into your daily workUnify and normalize problems from daily work
3Learn the work of your coleaguesIndirect working experience also help
4Popular products under the hoodOnce you notice an interesting feature, think about how it’s supported?
5Read engineering blogsEspecially for big companies
6Tools under the hoodCommon tools/frameworks
7Try toolsUse cases; Alternatives; Pros and Cons
8Read papersBest practices in papers
9Try new thingsGain hands-on experience; evaluate alternatives
10Datastore & OSLearn how databases and operating systems work
11Language implementationDeep dive into one programming language. Java, Python, Golang, etc

1.15 More Resources

Cs Interview Cheat Sheet 2019

License: Code is licensed under MIT License.

Interview Cheat Sheet For Managers

https://www.infoq.com/presentations/Pinterest/